DECLARE
l_k_header_rec oks_contracts_pub.header_rec_type;
l_header_contacts_tbl oks_contracts_pub.contact_tbl;
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_strm_level_tbl oks_bill_sch.streamlvl_tbl;
l_merge_rule VARCHAR2(50);
l_usage_instantiate VARCHAR2(50);
l_ib_creation VARCHAR2(1);
l_billing_sch_type VARCHAR2(50);
l_chrid NUMBER;
l_service_line_id NUMBER;
l_cp_line_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_sts_code VARCHAR2(10);
l_scs_code VARCHAR2(10) := 'WARRANTY';
l_tax_status_flag VARCHAR2(1) := 'S';
l_merge_type VARCHAR2(3) := 'NEW';
l_renewal_type VARCHAR2(3) := 'DNR';
l_line_number VARCHAR2(1) := '1';
l_line_type VARCHAR2(1) := 'W';
l_negotiated_amount NUMBER := 0;
l_net_amount NUMBER := 0;
l_sub_line_number VARCHAR2(5) := '1.1';
l_tmp_cle_id NUMBER;
l_user_id NUMBER := 0; -- SYSADMIN
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
Type header_rec_type
IS
Record
(
start_date DATE,
end_date DATE,
authoring_org_id NUMBER,
short_description VARCHAR2(1995),
party_id NUMBER,
bill_to_id NUMBER,
ship_to_id NUMBER,
price_list_id NUMBER,
cust_po_number VARCHAR2(240),
agreement_id NUMBER,
currency VARCHAR2(15),
accounting_rule_type NUMBER,
invoice_rule_type NUMBER,
order_hdr_id NUMBER,
order_number NUMBER,
payment_term_id NUMBER,
cvn_type VARCHAR2(25),
cvn_rate NUMBER,
cvn_date DATE,
Organization_id NUMBER,
contact_id NUMBER,
cust_account NUMBER);
TYPE header_tbl_type
IS
TABLE OF header_rec_type INDEX BY BINARY_INTEGER;
l_header_tbl header_tbl_type;
Type line_rec_type
IS
Record
(
order_line_id NUMBER,
start_date DATE,
end_date DATE,
srv_id NUMBER,
quantity NUMBER,
uom_code VARCHAR2(25));
TYPE line_tbl_type
IS
TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
l_line_tbl line_tbl_type;
Type subline_rec_type
IS
Record
(
customer_product_id NUMBER,
product_desc VARCHAR2(440));
TYPE subline_tbl_type
IS
TABLE OF subline_rec_type INDEX BY BINARY_INTEGER;
l_subline_tbl subline_tbl_type;
CURSOR header_cur
IS
SELECT
/*+ use_nl index (oe_order_headers_all, RESM_ORDER_HEADERS_N2) */
MIN(oola.actual_shipment_date + NVL(msib.service_starting_delay,0))start_date,
MAX(DECODE(UPPER(msi.primary_uom_code), 'YR', ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity*12), 'MTH',ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity), 'DAY',(oola.actual_shipment_date+Component_Quantity+NVL(msib.service_starting_delay,0)))-1 ) end_date,
ooha.org_id authoring_org_id,
'CUSTOMER : '
||hp.party_name
||' Warranty/Extended Warranty Contract' short_description,
hp.party_id party_id,
ooha.invoice_to_org_id bill_to_id,
ooha.ship_to_org_id ship_to_id,
ooha.price_list_id,
ooha.cust_po_number,
ooha.agreement_id,
ooha.transactional_curr_code currency,
ooha.accounting_rule_id accounting_rule_type,
ooha.invoicing_rule_id invoice_rule_type ,
ooha.header_id order_hdr_id,
ooha.order_number,
ooha.payment_term_id,
ooha.conversion_type_code cvn_type,
ooha.conversion_rate cvn_rate,
ooha.conversion_rate_date cvn_date,
ooha.ship_from_org_id organization_id,
ooha.salesrep_id contact_id,
hca.cust_account_id cust_account
FROM mtl_system_items_b msi,
bom_structures_b bbm,
bom_components_b bic,
oe_order_lines_all oola,
mtl_system_items_b msib,
csi_item_instances cii,
oe_order_headers_all ooha,
oe_transaction_types_all ott,
hz_cust_accounts hca,
hz_parties hp
WHERE msi.vendor_warranty_flag = 'Y'
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = bbm.organization_id
AND bbm.bill_sequence_id = bic.bill_sequence_id
AND bic.disable_date IS NULL
AND bbm.assembly_item_id = oola.inventory_item_id
AND bbm.organization_id = oola.ship_from_org_id
AND oola.inventory_item_id = msib.inventory_item_id
AND oola.ship_from_org_id = msib.organization_id
AND oola.line_id = cii.last_oe_order_line_id
AND cii.instance_status_id <> 1
AND oola.header_id = ooha.header_id
AND ooha.order_type_id = ott.transaction_type_id
AND ooha.org_id = ott.org_id
AND ott.order_category_code <> 'RETURN'
AND ooha.order_source_id <> 7
AND ooha.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND ooha.order_number = '606104'
AND NOT EXISTS
(SELECT 1
FROM okc_k_items
WHERE jtot_object1_code = 'OKX_CUSTPROD'
AND to_number(object1_id1) = cii.instance_id
)
AND NOT EXISTS
(SELECT 1 FROM csi_txn_errors ctr WHERE ctr.instance_id = cii.instance_id
)
GROUP BY ooha.org_id,
hp.party_name,
hp.party_id,
ooha.invoice_to_org_id,
ooha.ship_to_org_id,
ooha.price_list_id,
ooha.cust_po_number,
ooha.agreement_id,
ooha.transactional_curr_code,
ooha.accounting_rule_id,
ooha.invoicing_rule_id ,
ooha.header_id,
ooha.order_number,
ooha.payment_term_id,
ooha.conversion_type_code,
ooha.conversion_rate,
ooha.conversion_rate_date,
ooha.ship_from_org_id,
ooha.salesrep_id,
hca.cust_account_id;
CURSOR line_cur(p_header_id NUMBER)
IS
SELECT oola.line_id order_line_id,
(oola.actual_shipment_date+ NVL(msib.service_starting_delay,0))start_date,
(DECODE(UPPER(msi.primary_uom_code), 'YR', ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity*12), 'MTH',ADD_MONTHS(oola.actual_shipment_date+NVL(msib.service_starting_delay,0),Component_Quantity), 'DAY',(oola.actual_shipment_date+Component_Quantity+NVL(msib.service_starting_delay,0)))-1 ) end_date,
msi.inventory_item_id srv_id,
bic.Component_Quantity quantity,
msi.primary_uom_code uom_code
FROM mtl_system_items_b msi,
bom_structures_b bbm,
bom_components_b bic,
oe_order_lines_all oola,
mtl_system_items_b msib
WHERE msi.vendor_warranty_flag = 'Y'
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = bbm.organization_id
AND bbm.bill_sequence_id = bic.bill_sequence_id
AND bic.disable_date IS NULL
AND bbm.assembly_item_id = oola.inventory_item_id
AND bbm.organization_id = oola.ship_from_org_id
AND oola.inventory_item_id = msib.inventory_item_id
AND oola.ship_from_org_id = msib.organization_id
AND msib.comms_nl_trackable_flag = 'Y'
AND oola.header_id = p_header_id;
CURSOR subline_cur(p_line_id NUMBER)
IS
SELECT cii.instance_id customer_product_id,
msib.segment1
||';'
||cii.serial_number
||';'
||cii.instance_id product_desc
FROM oe_order_lines_all oola,
csi_item_instances cii,
bom_structures_b bbm,
bom_components_b bic,
mtl_system_items_b msib
WHERE oola.line_id = cii.last_oe_order_line_id
AND oola.inventory_item_id = bbm.assembly_item_id
AND oola.ship_from_org_id = bbm.organization_id
AND bbm.bill_sequence_id = bic.bill_sequence_id
AND bic.disable_date IS NULL
AND msib.vendor_warranty_flag = 'Y'
AND msib.inventory_item_id = bic.component_item_id
AND msib.organization_id = bbm.organization_id
AND line_id = p_line_id;
CURSOR coverage_cur (p_item_id NUMBER, p_org_id NUMBER)
IS
SELECT coverage_template_id
FROM okx_system_items_v
WHERE id1 = p_item_id
AND organization_id = p_org_id;
CURSOR resp_cur
IS
SELECT responsibility_id,
application_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Service Contracts Manager (Corp) Config';
BEGIN
OPEN resp_cur;
FETCH resp_cur INTO l_resp_id,l_resp_appl_id;
CLOSE resp_cur;
fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
okc_context.set_okc_org_context;
okc_api.init_msg_list(OKC_API.G_TRUE);
OPEN header_cur;
FETCH header_cur BULK COLLECT INTO l_header_tbl;
CLOSE header_cur;
FOR i IN l_header_tbl.FIRST .. l_header_tbl.LAST
LOOP
IF (l_header_tbl(i).start_date > SYSDATE) THEN
l_sts_code := 'SIGNED';
ELSIF (l_header_tbl(i).end_date < SYSDATE) THEN
l_sts_code := 'EXPIRED';
ELSE
l_sts_code := 'ACTIVE';
END IF;
l_K_header_rec.start_date := l_header_tbl(i).start_date;
l_K_header_rec.end_date := l_header_tbl(i).end_date;
l_K_header_rec.sts_code := l_sts_code;
l_K_header_rec.scs_code := l_scs_code;
l_K_header_rec.authoring_org_id := l_header_tbl(i).authoring_org_id;
l_K_header_rec.short_description := l_header_tbl(i).short_description;
l_K_header_rec.party_id := l_header_tbl(i).party_id;
l_K_header_rec.bill_to_id := l_header_tbl(i).bill_to_id;
l_K_header_rec.ship_to_id := l_header_tbl(i).ship_to_id;
l_K_header_rec.price_list_id := l_header_tbl(i).price_list_id;
l_K_header_rec.cust_po_number := l_header_tbl(i).cust_po_number;
l_K_header_rec.agreement_id := l_header_tbl(i).agreement_id;
l_K_header_rec.currency := l_header_tbl(i).currency;
l_K_header_rec.accounting_rule_type := l_header_tbl(i).accounting_rule_type;
l_K_header_rec.invoice_rule_type := l_header_tbl(i).invoice_rule_type;
l_K_header_rec.order_hdr_id := l_header_tbl(i).order_hdr_id;
l_K_header_rec.payment_term_id := l_header_tbl(i).payment_term_id;
l_K_header_rec.cvn_type := l_header_tbl(i).cvn_type;
l_K_header_rec.cvn_rate := l_header_tbl(i).cvn_rate;
l_K_header_rec.cvn_date := l_header_tbl(i).cvn_date;
l_K_header_rec.tax_status_flag := l_tax_status_flag;
l_K_header_rec.merge_type := l_merge_type;
l_K_header_rec.renewal_type := l_renewal_type;
l_K_header_rec.organization_id := l_header_tbl(i).organization_id;
l_header_contacts_tbl(1).party_role := 'VENDOR';
l_header_contacts_tbl(1).contact_role := 'SALESPERSON';
l_header_contacts_tbl(1).contact_object_code := 'OKX_SALEPERS';
l_header_contacts_tbl(1).contact_id := l_header_tbl(i).contact_id;
oks_contracts_pub.create_contract_header(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,
x_chrid => l_chrid,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
dbms_output.put_line ('Error while creating the Contract Header for order#: '||l_header_tbl(i).order_number);
IF NVL(l_msg_count,0) > 0 THEN
FOR p IN 1..l_msg_count
LOOP
dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
END LOOP;
END IF;
ROLLBACK;
ELSE
OPEN line_cur(l_header_tbl(i).order_hdr_id);
FETCH line_cur BULK COLLECT INTO l_line_tbl;
CLOSE line_cur;
FOR j IN l_line_tbl.FIRST .. l_line_tbl.LAST
LOOP
IF (l_line_tbl(j).start_date > SYSDATE) THEN
l_sts_code := 'SIGNED';
ELSIF (l_line_tbl(j).end_date < SYSDATE) THEN
l_sts_code := 'EXPIRED';
ELSE
l_sts_code := 'ACTIVE';
END IF;
l_k_line_rec.k_hdr_id := l_chrid;
l_k_line_rec.k_line_number := j;
l_k_line_rec.line_sts_code := l_sts_code;
l_k_line_rec.cust_account := l_header_tbl(i).cust_account;
l_k_line_rec.org_id := l_header_tbl(i).authoring_org_id;
l_k_line_rec.organization_id := l_header_tbl(i).organization_id;
l_k_line_rec.bill_to_id := l_header_tbl(i).bill_to_id;
l_k_line_rec.ship_to_id := l_header_tbl(i).ship_to_id;
l_k_line_rec.order_line_id := l_line_tbl(j).order_line_id;
l_k_line_rec.accounting_rule_type := l_header_tbl(i).accounting_rule_type;
l_k_line_rec.invoicing_rule_type := l_header_tbl(i).invoice_rule_type;
l_k_line_rec.line_type := l_line_type;
l_k_line_rec.currency := l_header_tbl(i).currency;
l_k_line_rec.list_price := l_header_tbl(i).price_list_id;
l_k_line_rec.negotiated_amount := l_negotiated_amount;
l_k_line_rec.line_renewal_type := l_renewal_type;
l_k_line_rec.srv_id := l_line_tbl(j).srv_id;
l_k_line_rec.srv_sdt := l_line_tbl(j).start_date;
l_k_line_rec.srv_edt := l_line_tbl(j).end_date;
l_k_line_rec.customer_id := l_header_tbl(i).party_id;
l_k_line_rec.start_date_active := l_line_tbl(j).start_date;
l_k_line_rec.end_date_active := l_line_tbl(j).end_date;
l_k_line_rec.quantity := l_line_tbl(j).quantity;
l_k_line_rec.uom_code := l_line_tbl(j).uom_code;
l_k_line_rec.net_amount := l_net_amount;
l_k_line_rec.currency_code := l_header_tbl(i).currency;
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 => l_service_line_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
OPEN coverage_cur (l_line_tbl(j).srv_id, l_header_tbl(i).organization_id);
FETCH coverage_cur INTO l_tmp_cle_id;
CLOSE coverage_cur;
UPDATE oks_k_lines_b
SET Standard_Cov_YN = 'Y',
coverage_id = l_tmp_cle_id
WHERE cle_id = l_service_line_id;
OPEN subline_cur(l_line_tbl(j).order_line_id);
FETCH subline_cur BULK COLLECT INTO l_subline_tbl;
CLOSE subline_cur;
FOR k IN l_subline_tbl.FIRST .. l_subline_tbl.LAST
LOOP
l_K_covd_rec.k_id := l_chrid;
l_K_covd_rec.Attach_2_Line_id := l_service_line_id;
l_k_covd_rec.line_number := j||'.'||k;
l_k_covd_rec.product_sts_code := l_sts_code;
l_k_covd_rec.customer_product_id := l_subline_tbl(k).customer_product_id;
l_k_covd_rec.product_desc := l_subline_tbl(k).product_desc;
l_k_covd_rec.product_start_date := l_line_tbl(j).start_date;
l_k_covd_rec.product_end_date := l_line_tbl(j).end_date;
l_k_covd_rec.quantity := l_line_tbl(j).quantity;
l_k_covd_rec.uom_code := l_line_tbl(j).uom_code;
l_k_covd_rec.list_price := l_header_tbl(i).price_list_id;
l_k_covd_rec.negotiated_amount := l_negotiated_amount;
l_k_covd_rec.currency_code := l_header_tbl(i).currency;
l_k_covd_rec.line_renewal_type := l_renewal_type;
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 => l_cp_line_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
dbms_output.put_line ('Error while creating the Contract Subline for order#: '||l_header_tbl(i).order_number);
IF NVL(l_msg_count,0) > 0 THEN
FOR p IN 1..l_msg_count
LOOP
dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
END LOOP;
END IF;
EXIT;
END IF;
END LOOP;
END IF;
IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
dbms_output.put_line ('Error while creating the Contract Line for order#: '||l_header_tbl(i).order_number);
IF NVL(l_msg_count,0) > 0 THEN
FOR p IN 1..l_msg_count
LOOP
dbms_output.put_line('Error: '||fnd_msg_pub.get(p, 'F'));
END LOOP;
END IF;
ROLLBACK;
EXIT;
END IF;
END LOOP;
dbms_output.put_line('Contract Created Successfully for Order#'||l_header_tbl(i).order_number||'. Contract ID: '||l_chrid);
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error: '||SQLERRM);
ROLLBACK;
END;
/
Tuesday, 12 June 2018
Script to create Service Contract with Line and Sub Line
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment