Friday, February 4, 2011

Source Code: For Warranty contract creation for install base item

DECLARE

l_K_header_rec OKS_CONTRACTS_PUB.header_rec_type;
l_header_contacts_tbl OKS_CONTRACTS_PUB.contact_tbl;
hcon INTEGER := 1;
l_header_sales_crd_tbl OKS_CONTRACTS_PUB.SalesCredit_tbl;
l_header_articles_tbl OKS_CONTRACTS_PUB.obj_articles_tbl;
l_K_line_rec OKS_CONTRACTS_PUB.line_rec_type;
l_line_contacts_tbl OKS_CONTRACTS_PUB.contact_tbl;
l_line_sales_crd_tbl OKS_CONTRACTS_PUB.SalesCredit_tbl;
l_K_Support_rec OKS_CONTRACTS_PUB.line_rec_type;
l_Support_contacts_tbl OKS_CONTRACTS_PUB.contact_tbl;
l_Support_sales_crd_tbl OKS_CONTRACTS_PUB.SalesCredit_tbl;
l_K_covd_rec OKS_CONTRACTS_PUB.Covered_level_Rec_Type;
l_price_attribs_in OKS_CONTRACTS_PUB.pricing_attributes_type;
l_merge_rule Varchar2(100);
l_usage_instantiate Varchar2(100);
l_ib_creation Varchar2(100);
l_Strm_hdr_rec OKS_BILL_SCH.StreamHdr_type;
l_strm_level_tbl OKS_BILL_SCH.StreamLvl_tbl;
lx_chrid Number;
lx_service_line_id Number;
lx_cp_line_id Number;
lx_return_status Varchar2(1);
lx_msg_count Number;
lx_msg_data Varchar2(2000);
lx_msg_tbl okc_qa_check_pub.msg_tbl_type;
l_descr okc_k_headers_tl.short_description%TYPE;
l_contract_number okc_k_headers_b.contract_number%TYPE;
l_qa_error BOOLEAN;

exc_failed EXCEPTION;

BEGIN

dbms_output.enable(1000000);

-- fnd_global.apps_initialize(1701 -- CBS_CONVERSION
-- ,21707 -- OKC_MANAGER
-- ,510); -- OKC

xxcb_c00_common_pkg.init_job('C04','DK',100);
xxcb_c00_common_pkg.g_debug_on := TRUE;

okc_context.set_okc_org_context;

okc_api.init_msg_list(OKC_API.G_TRUE);

savepoint start_contract;

l_descr := 'CBS API Test '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS');

l_K_header_rec.contract_number := l_contract_number;
l_K_header_rec.start_date := TRUNC(sysdate);
l_K_header_rec.end_date := TRUNC(sysdate)+365;
l_K_header_rec.sts_code := 'ENTERED';
l_K_header_rec.scs_code := 'SERVICE';
l_K_header_rec.authoring_org_id := 85; -- CANON DENMARK
l_K_header_rec.short_description := l_descr;
l_K_header_rec.chr_group := 2; -- CBS DK Service Contracts
l_K_header_rec.pdf_id := 3; -- Approval Workflow OKCAUKAP
l_K_header_rec.party_id := 1108; -- Lego Systems
l_K_header_rec.bill_to_id := 1039; -- Lego Systems 1039
l_K_header_rec.ship_to_id := 1041; -- Lego Systems 1041
l_K_header_rec.price_list_id := 6112; -- CBS DK Standard Pricelist
l_K_header_rec.agreement_id := 44; -- Lego Systems Agreement
l_K_header_rec.currency := 'DKK'; -- Danish Kroner
l_K_header_rec.accounting_rule_type:= 1002; -- 12 MONTHS ADVANCE
l_K_header_rec.invoice_rule_type := -2; -- Advance Invoice
l_K_header_rec.payment_term_id := 1024; -- 1ST OF MONTH
l_K_header_rec.contact_id := 1131; -- Peter Parker
l_K_header_rec.merge_type := 'NEW';
l_K_header_rec.merge_object_id := NULL;
l_K_header_rec.Ar_interface_yn := 'Y';
l_K_header_rec.transaction_type := 1089; -- SERV-Invoice
l_K_header_rec.Summary_invoice_yn := 'N';
l_K_header_rec.qcl_id := 1;



l_header_contacts_tbl(hcon).party_role := 'CUSTOMER';
l_header_contacts_tbl(hcon).contact_role := 'USER';
l_header_contacts_tbl(hcon).contact_object_code := 'OKX_PCONTACT';
l_header_contacts_tbl(hcon).contact_id := 1131; -- Peter Parker

hcon := hcon + 1;

l_header_contacts_tbl(hcon).party_role := 'VENDOR';
l_header_contacts_tbl(hcon).contact_role := 'SALESPERSON';
l_header_contacts_tbl(hcon).contact_object_code := 'OKX_SALEPERS';
l_header_contacts_tbl(hcon).contact_id := 100000000; -- Lange, MRS. Dagmar



l_K_line_rec.k_line_number := '1';
l_K_line_rec.line_sts_code := 'ACTIVE';
l_K_line_rec.cust_account := '1021';
l_K_line_rec.org_id := 85; -- CANON DENMARK
l_K_line_rec.organization_id := 90; -- CANON DENMARK
l_K_line_rec.bill_to_id := 1039; -- Lego Systems 1039
l_K_line_rec.ship_to_id := 1041; -- Lego Systems 1041
l_K_line_rec.accounting_rule_type := 1002; -- 12 MONTHS ADVANCE
l_K_line_rec.invoicing_rule_type := -2; -- Advance Invoice
l_K_line_rec.line_type := 'S'; -- Service ---E,U.W,S,SB,SU
l_K_line_rec.currency := 'DKK'; -- Danish Kroner
l_K_line_rec.list_price := 24;
l_K_line_rec.negotiated_amount := 24;
l_K_line_rec.customer_product_id := 10884; -- From Install Base
l_K_line_rec.customer_id := 1108; -- Lego
l_K_line_rec.start_date_active := TRUNC(sysdate);
l_K_line_rec.end_date_active := TRUNC(sysdate)+365;
l_K_line_rec.quantity := 1;
l_K_line_rec.net_amount := 24;
l_K_line_rec.srv_id := 926; -- Monthly Warranty
l_K_line_rec.srv_sdt := TRUNC(sysdate);
l_K_line_rec.srv_edt := TRUNC(sysdate)+365;


l_line_sales_crd_tbl(1).ctc_id := 100000000; -- Lange, MRS. Dagmar
l_line_sales_crd_tbl(1).sales_credit_type_id := 1; -- Quota Sales Credit
l_line_sales_crd_tbl(1).percent := 100;



l_K_covd_rec.line_number := '1.1';
l_K_covd_rec.product_sts_code := 'ACTIVE';
l_K_covd_rec.Customer_Product_Id := 10884;
l_K_covd_rec.Product_Desc := '7655A002AA;TEST11;10884';
l_K_covd_rec.Product_Start_Date := TRUNC(sysdate);
l_K_covd_rec.Product_End_Date := TRUNC(sysdate)+365;
l_K_covd_rec.Quantity := 1;
l_K_covd_rec.settlement_flag := 'N';
l_K_covd_rec.average_bill_flag := 'N';
l_K_covd_rec.Uom_Code := 'YR';
l_K_covd_rec.list_price := 24;
l_K_covd_rec.negotiated_amount := 24;
l_K_covd_rec.currency_code := 'DKK';
l_K_covd_rec.period := 'Year';



l_Strm_hdr_rec.Rule_Information1 := 'P'; -- Bill Type T E P T = Top Level
l_Strm_hdr_rec.Rule_Information_Category := 'SLH';


l_strm_level_tbl(1).Rule_Id := -2; -- Advance Invoice
l_strm_level_tbl(1).Rule_Information1 := '10'; -- Seq No
l_strm_level_tbl(1).Rule_Information2 := '25-MAR-2003'; -- Start Date
l_strm_level_tbl(1).Rule_Information3 := '12'; -- Periods
l_strm_level_tbl(1).Rule_Information4 := '1'; -- UOM/Period
l_strm_level_tbl(1).Rule_Information6 := 2; -- Amount i.e. 24 / 12
l_strm_level_tbl(1).Rule_Information_Category := 'SLL';
l_strm_level_tbl(1).Object1_Id1 := 'MTH'; -- UOM
l_strm_level_tbl(1).Jtot_Object1_Code := 'OKS_TUOM';



OKS_CONTRACTS_PUB.create_contract
(p_K_header_rec => l_K_header_rec
,p_header_contacts_tbl => l_header_contacts_tbl
,p_header_sales_crd_tbl => l_header_sales_crd_tbl
,p_header_articles_tbl => l_header_articles_tbl
,p_K_line_rec => l_K_line_rec
,p_line_contacts_tbl => l_line_contacts_tbl
,p_line_sales_crd_tbl => l_line_sales_crd_tbl
,p_K_Support_rec => l_K_Support_rec
,p_Support_contacts_tbl => l_Support_contacts_tbl
,p_Support_sales_crd_tbl => l_Support_sales_crd_tbl
,p_K_covd_rec => l_K_covd_rec
,p_price_attribs_in => l_price_attribs_in
,p_merge_rule => l_merge_rule
,p_usage_instantiate => l_usage_instantiate
,p_ib_creation => l_ib_creation
,p_Strm_hdr_rec => l_Strm_hdr_rec
,p_strm_level_tbl => l_strm_level_tbl
,x_chrid => lx_chrid
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);

dbms_output.put_line('Contract Return Status is:'||lx_return_status);
dbms_output.put_line('Message Count is:'||to_char(lx_msg_count));

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count LOOP
dbms_output.put_line('OKS API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
RAISE exc_failed;
END IF;


l_K_line_rec.k_hdr_id := lx_chrid;
l_K_line_rec.k_line_number := '2';
l_K_line_rec.line_sts_code := 'ACTIVE';
l_K_line_rec.cust_account := '1021';
l_K_line_rec.org_id := 85; -- CANON DENMARK
l_K_line_rec.organization_id := 90; -- CANON DENMARK
l_K_line_rec.bill_to_id := 1039; -- Lego Systems 1039
l_K_line_rec.ship_to_id := 1041; -- Lego Systems 1041
l_K_line_rec.accounting_rule_type := 1002; -- 12 MONTHS ADVANCE
l_K_line_rec.invoicing_rule_type := -2; -- Advance Invoice
l_K_line_rec.line_type := 'U'; -- Usage ---E,U.W,S,SB,SU
l_K_line_rec.currency := 'DKK'; -- Danish Kroner
l_K_line_rec.list_price := 24;
l_K_line_rec.negotiated_amount := 24;
l_K_line_rec.customer_product_id := 10884; -- From Install Base
l_K_line_rec.customer_id := 1108; -- Lego
l_K_line_rec.start_date_active := TRUNC(sysdate);
l_K_line_rec.end_date_active := TRUNC(sysdate)+365;
l_K_line_rec.quantity := 1;
l_K_line_rec.net_amount := 0;
l_K_line_rec.srv_id := 374; -- BK Usage
l_K_line_rec.srv_sdt := TRUNC(sysdate);
l_K_line_rec.srv_edt := TRUNC(sysdate)+365;
l_K_line_rec.usage_type := 'FRT';
l_K_line_rec.usage_period := 'YR';


l_line_sales_crd_tbl(1).ctc_id := 100000000; -- Lange, MRS. Dagmar
l_line_sales_crd_tbl(1).sales_credit_type_id := 1; -- Quota Sales Credit
l_line_sales_crd_tbl(1).percent := 100;


OKS_CONTRACTS_PUB.Create_Service_Line
(p_k_line_rec => l_k_line_rec
,p_Contact_tbl => l_line_contacts_tbl
,p_line_sales_crd_tbl => l_line_sales_crd_tbl
,x_service_line_id => lx_service_line_id
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);


dbms_output.put_line('Service Return Status is:'||lx_return_status);
dbms_output.put_line('Message Count is:'||to_char(lx_msg_count));

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count LOOP
dbms_output.put_line('OKS API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
RAISE exc_failed;
END IF;

l_K_covd_rec.k_id := lx_chrid;
l_K_covd_rec.Attach_2_Line_id := lx_service_line_id;
l_K_covd_rec.line_number := '2.1';
l_K_covd_rec.product_sts_code := 'ACTIVE';
l_K_covd_rec.Customer_Product_Id := 283; -- Counter Id
l_K_covd_rec.Product_Desc := '7655A002AA';
l_K_covd_rec.Product_Start_Date := TRUNC(sysdate);
l_K_covd_rec.Product_End_Date := TRUNC(sysdate)+365;
l_K_covd_rec.Quantity := 1;
l_K_covd_rec.settlement_flag := 'N';
l_K_covd_rec.average_bill_flag := 'N';
l_K_covd_rec.Uom_Code := 'YR';
l_K_covd_rec.list_price := 0;
l_K_covd_rec.negotiated_amount := 0;
l_K_covd_rec.currency_code := 'DKK';
l_K_covd_rec.period := 'Year';
l_K_covd_rec.fixed_qty := '1';



OKS_CONTRACTS_PUB.Create_Covered_Line
(p_k_covd_rec => l_k_covd_rec
,p_price_attribs => l_price_attribs_in
,x_cp_line_id => lx_cp_line_id
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
);

dbms_output.put_line('Covered Return Status is:'||lx_return_status);

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count LOOP
dbms_output.put_line('OKS API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
RAISE exc_failed;
END IF;


l_Strm_hdr_rec.Chr_Id := lx_chrid;
l_Strm_hdr_rec.Cle_Id := lx_service_line_id;
l_Strm_hdr_rec.Rule_Information1 := 'P'; -- Bill Type T E P T = Top Level
l_Strm_hdr_rec.Rule_Information_Category := 'SLH'; -- was SLL


l_strm_level_tbl(1).Rule_Id := -2; -- Advance Invoice
l_strm_level_tbl(1).Rule_Information1 := '10'; -- Seq No
l_strm_level_tbl(1).Rule_Information2 := '25-MAR-2003'; -- Start Date
l_strm_level_tbl(1).Rule_Information3 := '12'; -- Periods
l_strm_level_tbl(1).Rule_Information4 := '1'; -- UOM/Period
l_strm_level_tbl(1).Rule_Information6 := 0; -- Amount i.e. 12 / 12
l_strm_level_tbl(1).Rule_Information_Category := 'SLL'; -- Was IRE
l_strm_level_tbl(1).Object1_Id1 := 'MTH'; -- UOM
l_strm_level_tbl(1).Jtot_Object1_Code := 'OKS_TUOM';


OKS_CONTRACTS_PUB.Create_Bill_Schedule
(p_Strm_hdr_rec => l_Strm_hdr_rec
,p_strm_level_tbl => l_strm_level_tbl
,p_invoice_rule_id => -2 -- Advance Invoice
,x_return_status => lx_return_status
);

dbms_output.put_line('Bill Schedule Return Status is:'||lx_return_status);

IF lx_return_status != 'S' THEN
RAISE exc_failed;
END IF;


okc_qa_check_pub.execute_qa_check_list
(p_api_version => 1.0
,p_init_msg_list => OKC_API.G_TRUE
,x_return_status => lx_return_status
,x_msg_count => lx_msg_count
,x_msg_data => lx_msg_data
,p_qcl_id => 1 -- DEFAULT QA CHECK LIST
,p_chr_id => lx_chrid
,p_override_flag => 'N'
,x_msg_tbl => lx_msg_tbl
);

IF lx_return_status != 'S' THEN
IF NVL(lx_msg_count,0) > 0 THEN
FOR i IN 1..lx_msg_count
LOOP
dbms_output.put_line('QA API:'||fnd_msg_pub.get(i, 'F'));
END LOOP;
END IF;
ELSE
l_qa_error := FALSE;

FOR i in NVL(lx_msg_tbl.FIRST,0)..NVL(lx_msg_tbl.LAST,-1)
LOOP
IF lx_msg_tbl(i).error_status = 'E' THEN -- Only interested in Errors (E)
l_qa_error := TRUE;
dbms_output.put_line('QA Check Error Message: '||TO_CHAR(i));
dbms_output.put_line('Name: '||lx_msg_tbl(i).name);
dbms_output.put_line('Data: '||lx_msg_tbl(i).data);
dbms_output.put_line(' ');
END IF;
END LOOP;

IF l_qa_error THEN
dbms_output.put_line('Failed QA');
RAISE exc_failed;
END IF;
END IF;


okc_contract_approval_pub.k_approved
(p_contract_id => lx_chrid
,p_date_approved => sysdate
,x_return_status => lx_return_status
);
IF lx_return_status != 'S' THEN
dbms_output.put_line('Unable to Approve');
RAISE exc_failed;
END IF;

okc_contract_approval_pub.k_signed
(p_contract_id => lx_chrid
,p_date_signed => sysdate
,x_return_status => lx_return_status
);
IF lx_return_status != 'S' THEN
dbms_output.put_line('Unable to Sign');
RAISE exc_failed;
END IF;

dbms_output.put_line('Contract Approved and Signed');
commit;

SELECT contract_number
INTO l_contract_number
FROM okc_k_headers_b
WHERE id = lx_chrid;

dbms_output.put_line('Contract No:'||l_contract_number);

xxcb_c00_common_pkg.row_status('LOADED');

xxcb_c00_common_pkg.show_stats;

EXCEPTION
WHEN exc_failed THEN
dbms_output.put_line('Rolled back to savepoint');
rollback to savepoint start_contract;
xxcb_c00_common_pkg.row_status('REJECTED');

WHEN OTHERS THEN
dbms_output.put_line('When Others:'||SQLERRM);
END;

No comments:

Post a Comment