[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT invoiceable_item_flag
INTO l_flag
FROM mtl_system_items_b
WHERE organization_id = l_so_org_id and inventory_item_id = (select inventory_item_id from oe_order_lines_all where line_id = l_so_line_id);
SELECT SOURCE_TP_LOCATION_CODE
FROM ECX_TP_DETAILS_V
WHERE TRANSACTION_TYPE = 'XNB' AND TRANSACTION_SUBTYPE = 'CBODI';
SELECT sold_to_org_id
INTO l_cust_acct_id
FROM oe_order_lines_all
WHERE line_id = l_so_doc_id;
SELECT account_number
INTO l_acct_num
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_acct_id;
SELECT count(collaboration_id)
INTO l_cnt
FROM cln_coll_hist_hdr
WHERE document_no = l_acct_num;
select COUNT(clndtl.collaboration_dtl_id)
into l_cnt
from cln_coll_hist_hdr clnhdr,
cln_coll_hist_dtl clndtl
where
clnhdr.application_id = '881'
and clnhdr.collaboration_type = 'XNB_ACCOUNT'
and clnhdr.document_no = l_acct_num
and clnhdr.collaboration_id = clndtl.collaboration_id
and clndtl.collaboration_document_type = 'CONFIRM_BOD'
and clndtl.originator_reference = l_tp_code
and clndtl.document_status = 'SUCCESS';
select COUNT(clndtl.collaboration_dtl_id)
into l_cnt_t
from cln_coll_hist_hdr clnhdr,
cln_coll_hist_dtl clndtl
where
clnhdr.application_id = '881'
and clnhdr.collaboration_type = 'XNB_ACCOUNT'
and clnhdr.document_no = l_acct_num
and clnhdr.collaboration_id = clndtl.collaboration_id
and clndtl.collaboration_document_type = 'CONFIRM_BOD'
and clndtl.originator_reference = l_tp_code
and clndtl.document_status = 'ERROR';
/* Inserts the publishing related data into the table xnb_bill_to_party_details */
PROCEDURE publish_bill_to_address( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2)
AS
l_inv_to_org_id NUMBER;
SELECT DISTINCT t1.site_use_id
FROM
hz_cust_site_uses_all t1,
hz_cust_acct_sites_all t2
WHERE t1.cust_acct_site_id = t2.cust_acct_site_id
AND t2.cust_account_id = pl_sold_to_org_id
AND t1.org_id = pl_org_id
AND t1.site_use_code = 'BILL_TO';
SELECT invoice_to_org_id,
sold_to_org_id
INTO l_inv_to_org_id,
l_sold_to_org_id
FROM oe_order_lines_all
WHERE line_id = l_doc_id; --DOCUMENT_ID
SELECT t1.site_use_id
INTO l_pri_bill_to_site_id
FROM hz_cust_site_uses_all t1,
hz_cust_acct_sites_all t2
WHERE t1.site_use_code = 'BILL_TO'
AND t1.primary_flag = 'Y'
AND t1.status = 'A'
AND t1.org_id = l_org_id
AND t1.cust_acct_site_id = t2.cust_acct_site_id
AND t2.cust_account_id = l_sold_to_org_id;
SELECT c.party_id,
c.party_number,
c.party_name,
b.cust_account_id,
b.account_number,
b.account_name,
locations.address1||DECODE(locations.address2
, NULL
, NULL
, ';'||locations.address2|| DECODE(locations.address3
/***** Private API to insert the sales Order Data to be published into the table */
/* xnb_bill_to_party_details */
/* */
PROCEDURE create_sales_order
(
l_doc_id IN NUMBER,
l_party_id IN NUMBER,
l_account_id IN NUMBER,
l_party_number IN VARCHAR2,
l_party_name IN VARCHAR2,
l_account_number IN VARCHAR2,
l_account_name IN VARCHAR2,
l_bill_to_address IN VARCHAR2,
l_country IN VARCHAR2,
l_state IN VARCHAR2,
l_county IN VARCHAR2,
l_city IN VARCHAR2,
l_postal_code IN VARCHAR2,
l_primary_bill_to_flag IN CHAR,
l_bill_to_owner_flag IN CHAR,
x_result OUT NOCOPY NUMBER
)
AS
l_sql VARCHAR2(5000);
--Insert the records to XNB_BILL_TO_PARTY_DETAILS
--
-------------------------------------------------------------------------------------------
l_sql := 'INSERT INTO xnb_bill_to_party_details'||
'(PARTY_ATTRIBUTE1, '||
'PARTY_ATTRIBUTE2, '||
'PARTY_ATTRIBUTE3, '||
'PARTY_ATTRIBUTE4, '||
'PARTY_ATTRIBUTE5, '||
'PARTY_ATTRIBUTE6, '||
'PARTY_ATTRIBUTE7, '||
'PARTY_ATTRIBUTE8, '||
'PARTY_ATTRIBUTE9, '||
'PARTY_ATTRIBUTE10, '||
'PARTY_ATTRIBUTE11, '||
'PARTY_ATTRIBUTE12, '||
'PARTY_ATTRIBUTE13, '||
'PARTY_ATTRIBUTE14, '||
'PARTY_ATTRIBUTE15, '||
'ACCT_ATTRIBUTE1, '||
'ACCT_ATTRIBUTE2, '||
'ACCT_ATTRIBUTE3, '||
'ACCT_ATTRIBUTE4, '||
'ACCT_ATTRIBUTE5, '||
'ACCT_ATTRIBUTE6, '||
'ACCT_ATTRIBUTE7, '||
'ACCT_ATTRIBUTE8, '||
'ACCT_ATTRIBUTE9, '||
'ACCT_ATTRIBUTE10, '||
'ACCT_ATTRIBUTE11, '||
'ACCT_ATTRIBUTE12, '||
'ACCT_ATTRIBUTE13, '||
'ACCT_ATTRIBUTE14, '||
'ACCT_ATTRIBUTE15, '||
'ORDER_LINE_ID, '||
'PARTY_NUMBER, '||
'PARTY_NAME, '||
'ACCOUNT_NUMBER, '||
'ACCOUNT_NAME, '||
'PRIMARY_BILL_TO_FLAG, '||
'BILL_TO_OWNER_FLAG, '||
'BILL_TO_ADDRESS, '||
'COUNTRY, '||
'STATE, '||
'COUNTY, '||
'CITY, '||
'POSTAL_CODE) '||
'(SELECT '||
'A.ATTRIBUTE1, '||
'A.ATTRIBUTE2, '||
'A.ATTRIBUTE3, '||
'A.ATTRIBUTE4, '||
'A.ATTRIBUTE5, '||
'A.ATTRIBUTE6, '||
'A.ATTRIBUTE7, '||
'A.ATTRIBUTE8, '||
'A.ATTRIBUTE9, '||
'A.ATTRIBUTE10, '||
'A.ATTRIBUTE11, '||
'A.ATTRIBUTE12, '||
'A.ATTRIBUTE13, '||
'A.ATTRIBUTE14, '||
'A.ATTRIBUTE15, '||
'B.ATTRIBUTE1, '||
'B.ATTRIBUTE2, '||
'B.ATTRIBUTE3, '||
'B.ATTRIBUTE4, '||
'B.ATTRIBUTE5, '||
'B.ATTRIBUTE6, '||
'B.ATTRIBUTE7, '||
'B.ATTRIBUTE8, '||
'B.ATTRIBUTE9, '||
'B.ATTRIBUTE10, '||
'B.ATTRIBUTE11, '||
'B.ATTRIBUTE12, '||
'B.ATTRIBUTE13, '||
'B.ATTRIBUTE14, '||
'B.ATTRIBUTE15, '''||l_doc_id||''','''||l_party_number||''','''||l_party_name||''','''||l_account_number||''','''||
l_account_name||''','''||l_primary_bill_to_flag||''','''||l_bill_to_owner_flag||''','''||l_bill_to_address||''','''||
l_country||''','''||l_state||''','''||l_county||''','''||l_city||''','''||l_postal_code||''''||
' FROM HZ_PARTIES A, HZ_CUST_ACCOUNTS B '||
' WHERE A.PARTY_ID = B.PARTY_ID AND A.PARTY_ID = '||l_party_id||' AND B.CUST_ACCOUNT_ID = '||l_account_id||')';
--Query to Delete the Published details
--
------------------------------------------------------------------------------
DELETE FROM xnb_bill_to_party_details
WHERE order_line_id = l_doc_id;
select install_location_type_code
into l_loc_type_code
from csi_item_instances
where instance_id = p_instance_id;
SELECT install_location_id
into l_install_loc_id
from csi_item_instances
where instance_id = p_instance_id;
SELECT location_id
into l_loc_id
from hz_party_sites
where party_site_id = l_install_loc_id;
SELECT ADDRESS1||DECODE(ADDRESS2
, NULL
, NULL
, ';'||ADDRESS2|| DECODE(ADDRESS3
SELECT install_location_id
into l_install_loc_id
from csi_item_instances
where instance_id = p_instance_id;
SELECT ADDRESS1||DECODE(ADDRESS2
, NULL
, NULL
, ';'||ADDRESS2|| DECODE(ADDRESS3
SELECT locations.address1||DECODE(locations.address2
, NULL
, NULL
, ';'||locations.address2|| DECODE(locations.address3
SELECT DISTINCT t1.site_use_id
FROM
hz_cust_site_uses_all t1,
hz_cust_acct_sites_all t2
WHERE t1.cust_acct_site_id = t2.cust_acct_site_id
AND t2.cust_account_id = pl_sold_to_org_id
AND t1.org_id = pl_org_id
AND t1.site_use_code = 'BILL_TO';
SELECT line.line_id
FROM oe_order_headers_all head,
oe_order_lines_all line,
mtl_system_items_vl item
WHERE head.order_number = p_order_num
AND head.header_id = line.header_id
AND line.inventory_item_id = item.inventory_item_id
AND item.organization_id = line.ship_from_org_id
AND item.invoiceable_item_flag = 'N';
SELECT invoice_to_org_id,
sold_to_org_id
INTO l_inv_to_org_id,
l_sold_to_org_id
FROM oe_order_lines_all
WHERE line_id = l_line_id; --DOCUMENT_ID
SELECT t1.site_use_id
INTO l_pri_bill_to_site_id
FROM hz_cust_site_uses_all t1,
hz_cust_acct_sites_all t2
WHERE t1.site_use_code = 'BILL_TO'
AND t1.primary_flag = 'Y'
AND t1.status = 'A'
AND t1.org_id = l_org_id
AND t1.cust_acct_site_id = t2.cust_acct_site_id
AND t2.cust_account_id = l_sold_to_org_id;
SELECT line_id
FROM oe_order_headers_all head,
oe_order_lines_all line
WHERE head.header_id = line.header_id
AND head.order_number = p_order_num;
--Query to Delete the Published details
--
------------------------------------------------------------------------------
OPEN l_line_ids (l_order_number);
DELETE FROM xnb_bill_to_party_details
WHERE order_line_id = l_line_id;