[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cust.cust_account_id,
party.party_name,
cust.account_number,
site.cust_acct_site_id,
cas.party_site_id
INTO x_customer_id,
x_customer_name,
x_customer_number,
x_customer_site_id,
x_party_site_id
FROM
hz_cust_site_uses site,
hz_cust_acct_sites cas,
hz_cust_accounts cust,
hz_parties party
WHERE site.site_use_code = p_site_use_code
AND site_use_id = p_site_use_id
AND site.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = cust.cust_account_id
AND cust.party_id=party.party_id;
SELECT org_contact_id
INTO l_org_contact_id
FROM hz_org_contacts oc,
hz_cust_account_roles car,
hz_relationships r
WHERE r.party_id = car.party_id
AND r.relationship_id = oc.party_relationship_id
AND cust_account_role_id = p_cust_acct_role_id
AND r.directional_flag = 'F'
;
PROCEDURE INSERT_SYNC_HEADER
(
P_HEADER_REC OE_Order_PUB.Header_Rec_Type,
P_CHANGE_TYPE VARCHAR2,
p_req_id NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2
)
IS
l_itemkey NUMBER;
oe_debug_pub.add( ' ENTERING OE_SYNC_ORDER_PVT.INSERT_SYNC_HEADER :'||
'header id :'||P_HEADER_REC.HEADER_ID || ' p_change type :'||P_CHANGE_TYPE);
INSERT INTO oe_header_acks
(header_id
,acknowledgment_type
,last_ack_code
,request_id
,sold_to_org_id
,change_sequence
,flow_status_code
,orig_sys_document_ref
,order_number
,ordered_date
,org_id
,order_source_id
-- Start: Added for Enh. 7244277
,invoice_address_id -- Bug # 7622467
,price_list_id -- Bug # 7644412
-- End : Added for Enh. 7244277
,creation_date
,transactional_curr_code) -- 9182921
VALUES (
P_HEADER_REC.header_id
,'SEBL_SYNC'
,P_HEADER_REC.flow_status_code
,p_req_id
,p_header_rec.sold_to_org_id
,p_header_rec.change_sequence
,decode(p_change_type, 'APPLY', 'ON_HOLD',
'RELEASE', 'RELEASED',
p_header_rec.flow_status_code)
,p_header_rec.orig_sys_document_ref
,p_header_rec.order_number
,p_header_rec.ordered_date
,p_header_rec.org_id
,p_header_rec.order_source_id
-- Start: Added for Enh. 7244277
,l_bill_to_party_site_id
,p_header_rec.price_list_id
-- End : Added for Enh. 7244277
,sysdate
,p_header_rec.transactional_curr_code); -- Bug 9182921
oe_debug_pub.add( ' EXITING OE_SYNC_ORDER_PVT.INSERT_SYNC_HEADER');
, 'OE_SYNC_ORDER_PVT.INSERT_SYNC_HEADER'
);
PROCEDURE INSERT_SYNC_LINE
(
p_line_rec oe_order_pub.line_rec_type,
p_change_type varchar2,
p_req_id number,
x_return_status out NOCOPY varchar2
)
IS
l_itemkey number;
oe_debug_pub.add( ' ENTERING OE_SYNC_ORDER_PVT.INSERT_SYNC_LINE :'||
'line id :'||P_LINE_REC.LINE_ID || ' p_change type :'||P_CHANGE_TYPE||
'flow status: ' || p_line_rec.flow_status_code);
oe_debug_pub.add( 'Inserting into line acks.', 5);
INSERT INTO oe_line_acks
(header_id
,line_id
,acknowledgment_type
,last_ack_code
,request_id
,change_sequence
,flow_status_code
,ordered_quantity
,schedule_arrival_date
,schedule_ship_date
,config_header_id
,config_rev_nbr
,configuration_id
,orig_sys_document_ref
,orig_sys_line_ref
,orig_sys_shipment_ref
,split_from_line_id
,inventory_item_id
,org_id
,order_source_id
,order_quantity_uom
,top_model_line_id
,item_type_code
-- Start: enh. 7244277
,line_number
,tax_value
,agreement_id
,payment_term_id
,promise_date
,shipping_method_code
,shipment_priority_code
,freight_terms_code
,ship_to_customer_id
,ship_to_contact_id
,ship_to_org_id
,invoice_to_customer_id
,invoice_to_contact_id
,invoice_to_org_id
,unit_selling_price -- 7644412
,price_list_id -- 7644412
,unit_list_price -- 7644412
,unit_list_price_per_pqty -- 7644412
,unit_percent_base_price -- 7644412
,unit_selling_price_per_pqty -- 7644412
,pricing_date -- 7644412
,ship_to_address_id
-- End : enh. 7244277
,creation_date
-- O2C25
,ship_from_org_id
,ship_from_org
,ship_to_org
,invoice_to_org
,line_category_code) -- 9151484
VALUES(
p_line_rec.header_id
,decode(p_line_rec.item_type_code,'CONFIG',p_line_rec.top_model_line_id,p_line_rec.line_id)
,'SEBL_SYNC'
,p_line_rec.flow_status_code
,p_req_id
,p_line_rec.change_sequence
,decode(p_change_type, 'APPLY', 'ON_HOLD',
'RELEASE', 'RELEASED',
p_line_rec.flow_status_code
)
,p_line_rec.ordered_quantity
,p_line_rec.schedule_arrival_date
,p_line_rec.schedule_ship_date
,p_line_rec.config_header_id
,p_line_rec.config_rev_nbr
,p_line_rec.configuration_id
,p_line_rec.orig_sys_document_ref
,p_line_rec.orig_sys_line_ref
,p_line_rec.orig_sys_shipment_ref
,decode(p_line_rec.item_type_code,'CONFIG',l_parent_rec.split_from_line_id,p_line_rec.split_from_line_id)
,p_line_rec.inventory_item_id
,p_line_rec.org_id
,p_line_rec.order_source_id
,p_line_rec.order_quantity_uom
,p_line_rec.top_model_line_id
,p_line_rec.item_type_code
-- Start : Enh. 7244277
,p_line_rec.line_number
-- ,Decode(p_line_rec.line_category_code,'RETURN',-p_line_rec.tax_value, p_line_rec.tax_value) -- Bug 8977354
,p_line_rec.tax_value
,p_line_rec.agreement_id
,p_line_rec.payment_term_id
,p_line_rec.promise_date
,p_line_rec.shipping_method_code
,p_line_rec.shipment_priority_code
,p_line_rec.freight_terms_code
,o_ship_to_cust_id
,o_ship_to_prty_cntct_id
,p_line_rec.ship_to_org_id
,o_bill_to_cust_id
,o_bill_to_prty_cntct_id
,p_line_rec.invoice_to_org_id
,p_line_rec.unit_selling_price
,p_line_rec.price_list_id
,p_line_rec.unit_list_price
,p_line_rec.unit_list_price_per_pqty
,p_line_rec.unit_percent_base_price
,p_line_rec.unit_selling_price_per_pqty
,p_line_rec.pricing_date
,o_ship_to_party_site_id
-- End : enh. 7244277
,SYSDATE
-- O2C25
,p_line_rec.ship_from_org_id
,Oe_Genesis_Util.Inventory_Org(p_line_rec.ship_from_org_id)
,Oe_Genesis_Util.Inventory_Org(p_line_rec.ship_to_org_id)
,Oe_Genesis_Util.Inventory_Org(p_line_rec.invoice_to_org_id)
,p_line_rec.line_category_code -- 9151484
);
oe_debug_pub.add( ' EXITING OE_SYNC_ORDER_PVT.INSERT_SYNC_LINE');
, 'OE_SYNC_ORDER_PVT.INSERT_SYNC_LINE'
);
SELECT ohdr.header_id
, ohld.line_id
,'SEBL_SYNC'
,ohdr.flow_status_code
,ohdr.request_id
,ohdr.sold_to_org_id
,ohdr.change_sequence
,ohdr.orig_sys_document_ref
,ohdr.order_number
,ohdr.ordered_date
,ohdr.org_id
,ohdr.order_source_id
,ohld.released_flag
FROM oe_order_headers_all ohdr,
oe_order_holds_all ohld,
oe_order_sources osrc -- to remove hardcoding on order_source_id
WHERE ohdr.header_id = ohld.header_id
-- AND ohdr.order_source_id = 28
AND ohdr.order_source_id = osrc.order_source_id
and osrc.aia_enabled_flag = 'Y'
AND ohld.hold_source_id = p_hold_source_id
AND decode(p_change_type,'RELEASE',ohld.hold_release_id,-99)
= decode(p_change_type,'RELEASE',p_hold_release_id,-99)
-- AND ohdr.booked_flag = 'Y' bug 16041842
-- AND flow_status_code <> 'ENTERED' bug 16041842
ORDER BY ohld.header_id;
SELECT ooh.header_id
,ooh.line_id
FROM oe_order_holds ooh,
oe_order_headers_all h,
oe_order_sources osrc -- to remove hardcoding on order_source_id
WHERE h.header_id = ooh.header_id
-- AND h.order_source_id = 28
AND h.order_source_id = osrc.order_source_id
AND osrc.aia_enabled_flag = 'Y'
AND ooh.order_hold_id = p_order_hold_id;
oe_debug_pub.add('sync_header_line- APPLY- Inserting row into oe_line_acks for p_line_id');
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
INSERT_SYNC_lINE(P_LINE_rec => P_LINE_rec,
p_change_type => p_change_type,
p_req_id => l_itemkey, -- XXXX
X_RETURN_STATUS => L_RETURN_STATUS);
oe_debug_pub.add('sync_header_line -APPLY- Inserting into oe_line_acks for p_line_id is DONE'||l_return_status);
oe_debug_pub.add(' INSERT_SYNC_HEADER - inserting for apply holds ');
INSERT_SYNC_HEADER(p_header_rec => p_header_rec,
p_change_type => null, --TODO
p_req_id => l_itemkey, --XXXX
x_return_status => l_return_status);
oe_debug_pub.add('sync_header_line - APPLY-inserted into line acks');
oe_debug_pub.add('sync_header_line - Inserting row into oe_header_acks for header_id');
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
INSERT_SYNC_HEADER(p_header_rec => p_header_rec,
p_change_type => p_change_type,
p_req_id => l_itemkey, --XXXX
x_return_status => l_return_status);
oe_debug_pub.add('sync_header_line - Inserting into oe_header_acks for header_id is DONE');
oe_debug_pub.add('sync_header_line - Inserting row into oe_header_acks for p_hold_source_id');
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
INSERT INTO oe_header_acks
(header_id
,acknowledgment_type
,last_ack_code
,request_id
,sold_to_org_id
,change_sequence
,flow_status_code
,orig_sys_document_ref
,order_number
,ordered_date
,org_id
,order_source_id)
VALUES
(hdr_rec.header_id
,'SEBL_SYNC'
,hdr_rec.flow_status_code
,l_itemkey
,hdr_rec.sold_to_org_id
,hdr_rec.change_sequence
,decode(p_change_type, 'APPLY', 'ON_HOLD',
'RELEASE', 'RELEASED',
hdr_rec.flow_status_code)
,hdr_rec.orig_sys_document_ref
,hdr_rec.order_number
,hdr_rec.ordered_date
,hdr_rec.org_id
,hdr_rec.order_source_id);
oe_debug_pub.add('sync_header_line - after insert');
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
INSERT INTO oe_header_acks
(header_id
,acknowledgment_type
,last_ack_code
,request_id
,sold_to_org_id
,change_sequence
,flow_status_code
,orig_sys_document_ref
,order_number
,ordered_date
,org_id
,order_source_id)
VALUES
(hdr_rec.header_id
,'SEBL_SYNC'
,hdr_rec.flow_status_code
,l_itemkey
,hdr_rec.sold_to_org_id
,hdr_rec.change_sequence
,hdr_rec.flow_status_code
,hdr_rec.orig_sys_document_ref
,hdr_rec.order_number
,hdr_rec.ordered_date
,hdr_rec.org_id
,hdr_rec.order_source_id);
INSERT_SYNC_lINE(P_LINE_REC => L_LINE_REC,
p_change_type => p_change_type,
p_req_id => l_itemkey, --XXXX
X_RETURN_STATUS => L_RETURN_STATUS);
oe_debug_pub.add('sync_header_line - Line inserted');
oe_debug_pub.add('sync_header_line - Inserting into oe_header_acks for p_hold_source_id is DONE');
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
oe_debug_pub.add('sync_header_line -RELEASE-BEFORE INSERTING HEADER ');
INSERT_SYNC_HEADER(p_header_rec => l_header_rec,
p_change_type => null,
p_req_id => l_itemkey,
x_return_status => l_return_status);
oe_debug_pub.add('sync_header_line -RELEASE-BEFORE INSERTING LINE ');
INSERT_SYNC_LINE(P_LINE_REC => l_line_rec,
p_change_type => p_change_type,
p_req_id => l_itemkey,
X_RETURN_STATUS => L_RETURN_STATUS);
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey
from dual;
INSERT_SYNC_HEADER(p_header_rec => l_header_rec,
p_change_type => p_change_type,
p_req_id => l_itemkey, --XXXX
x_return_status => l_return_status);
oe_debug_pub.add('sync_header_line - inserted into header acks');
INSERT_SYNC_HEADER(p_header_rec => p_header_rec,
p_change_type => p_change_type,
p_req_id => p_hdr_req_id,
x_return_status => l_return_status);
oe_debug_pub.add('sync_header_line - inserted into line acks');
SELECT
---
-- Bug 10009062 Fix: Retrieve ORIG_SYS_DOCUMENT_REF from
-- Order Headers rather than from Header Acknowledgments.
---
ack.HEADER_ID, hdr.ORIG_SYS_DOCUMENT_REF, ack.ORDER_NUMBER,
ack.ORDERED_DATE ,ack.ORG_ID ,ack.CHANGE_DATE,
ack.CHANGE_SEQUENCE ,ack.SOLD_TO_ORG_ID ,ack.ORDER_SOURCE_ID,
ack.REQUEST_ID ,ack.ACKNOWLEDGMENT_TYPE ,ack.FLOW_STATUS_CODE,
ack.INVOICE_ADDRESS_ID, ack.PRICE_LIST_ID,
ack.TRANSACTIONAL_CURR_CODE --Bug 9182921
FROM oe_header_acks ack,
oe_order_headers_all hdr -- Bug 10009062 Fix
WHERE ack.request_id = p_hdr_req_id
AND hdr.header_id = ack.header_id;
SELECT
HEADER_ID, ORIG_SYS_DOCUMENT_REF, ORIG_SYS_LINE_REF,
CHANGE_DATE ,CHANGE_SEQUENCE ,ORDER_NUMBER,
SOLD_TO_ORG_ID ,CONFIGURATION_ID ,CONFIG_REV_NBR,
CONFIG_HEADER_ID ,CONFIG_LINE_REF ,TOP_MODEL_LINE_ID,
INVENTORY_ITEM_ID ,LINE_ID ,LINE_NUMBER,
ORDER_SOURCE_ID ,ORDERED_QUANTITY ,ORG_ID,
REQUEST_ID ,SCHEDULE_ARRIVAL_DATE ,SCHEDULE_SHIP_DATE,
ACKNOWLEDGMENT_TYPE ,FLOW_STATUS_CODE ,SPLIT_FROM_LINE_REF,
SPLIT_FROM_SHIPMENT_REF ,SPLIT_FROM_LINE_ID, TAX_VALUE,
AGREEMENT_ID, PAYMENT_TERM_ID, PROMISE_DATE, SHIP_FROM_ORG_ID,
SHIPPING_METHOD_CODE, SHIPMENT_PRIORITY_CODE, FREIGHT_TERMS_CODE,
SHIP_TO_CUSTOMER_ID, SHIP_TO_CONTACT_ID, SHIP_TO_ORG_ID,
INVOICE_TO_CUSTOMER_ID, INVOICE_TO_CONTACT_ID, INVOICE_TO_ORG_ID,
UNIT_SELLING_PRICE, PRICE_LIST_ID, UNIT_LIST_PRICE,
UNIT_LIST_PRICE_PER_PQTY, UNIT_PERCENT_BASE_PRICE,
UNIT_SELLING_PRICE_PER_PQTY, PRICING_DATE, SHIP_TO_ADDRESS_ID,
SHIP_FROM_ORG, SHIP_TO_ORG, INVOICE_TO_ORG,
ITEM_TYPE_CODE, -- 9131629
LINE_CATEGORY_CODE -- 9151484
FROM oe_line_acks
WHERE request_id = p_lin_req_id;
SELECT OE_XML_MESSAGE_SEQ_S.nextval /* New one to be seeded */
INTO l_itemkey
FROM DUAL;
l_event_name := 'oracle.apps.ont.genesis.outbound.update';