[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT po_wf_itemkey_s.nextval INTO l_seq_val FROM dual;
SELECT po_wf_itemkey_s.nextval INTO l_seq_val FROM dual;
SELECT poh.expected_receipt_date
INTO h_expected_receipt_date_ts
FROM POS_HEADERS_V poh,PO_VENDORS pov
WHERE poh.shipment_num = x_shipment_num AND
poh.vendor_id = pov.vendor_id AND
poh.vendor_id = to_number(x_vendor_id) AND
poh.vendor_site_id = to_number(x_vendor_site_id);
select Nvl(user_id, -1)
INTO l_user_id
FROM fnd_user
WHERE user_name = x_buyer_user_name;
SELECT Nvl(FND_PROFILE.value_specific('ENABLE_TIMEZONE_CONVERSIONS', l_user_id), 'N')
INTO l_timezone_conversion
FROM dual;
SELECT NVL(POR.AGENT_ID,POH.AGENT_ID)
FROM RCV_TRANSACTIONS_INTERFACE RTI,
RCV_HEADERS_INTERFACE RHI,
PO_HEADERS_ALL POH,
PO_RELEASES_ALL POR,
WF_USERS WUSR
WHERE POH.PO_HEADER_ID = RTI.PO_HEADER_ID AND
RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
POH.PO_HEADER_ID = POR.PO_HEADER_ID (+) AND
RHI.SHIPMENT_NUM = v_shipment_num AND
POH.VENDOR_ID= v_vendor_id AND
POH.VENDOR_SITE_ID=v_vendor_site_id AND
Nvl(POR.AGENT_ID, POH.AGENT_ID) = WUSR.orig_system_id AND
WUSR.orig_system = 'PER'
/** Added for BUG:11869868**/
AND (POR.PO_RELEASE_ID=RTI.PO_RELEASE_ID
OR POR.PO_RELEASE_ID IS NULL)
/***/
UNION
SELECT NVL(POR.AGENT_ID,POH.AGENT_ID)
FROM RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_HEADERS_ALL POH,
PO_RELEASES_ALL POR,
WF_USERS WUSR
WHERE
POH.PO_HEADER_ID = RSL.PO_HEADER_ID AND
RSL.SHIPMENT_HEADER_ID= RSH.SHIPMENT_HEADER_ID AND
POH.PO_HEADER_ID = POR.PO_HEADER_ID (+) AND
RSH.SHIPMENT_NUM=v_shipment_num AND
POH.VENDOR_ID= v_vendor_id AND
POH.VENDOR_SITE_ID=v_vendor_site_id AND
Nvl(POR.AGENT_ID, POH.AGENT_ID) = WUSR.orig_system_id AND
WUSR.orig_system = 'PER'
/** Added for BUG:11869868**/
AND (POR.PO_RELEASE_ID=RSL.PO_RELEASE_ID OR
POR.PO_RELEASE_ID IS NULL)
/******/;
SELECT DISTINCT poh.shipment_num,
pov.vendor_name,
poh.shipped_date,
To_char(poh.shipped_date,fnd_profile.Value_specific('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
'NLS_CALENDAR = '''
||Nvl(fnd_profile.Value_specific('FND_FORMS_USER_CALENDAR',fnd_global.user_id),
'GREGORIAN')
||''''),
poh.expected_receipt_date,
To_char(poh.expected_receipt_date,fnd_profile.Value_specific('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
'NLS_CALENDAR = '''
||Nvl(fnd_profile.Value_specific('FND_FORMS_USER_CALENDAR',fnd_global.user_id),
'GREGORIAN')
||''''),
poh.invoice_num,
poh.total_invoice_amount,
To_char(poh.invoice_date,fnd_profile.Value_specific('ICX_DATE_FORMAT_MASK',fnd_global.user_id),
'NLS_CALENDAR = '''
||Nvl(fnd_profile.Value_specific('FND_FORMS_USER_CALENDAR',fnd_global.user_id),
'GREGORIAN')
||''''),
poh.tax_amount,
poh.asn_type
INTO x_shipment_num,x_vendor_name,x_shipped_date,x_shipped_date_ts,
x_expected_receipt_date,x_expected_receipt_ts,x_invoice_num,x_total_invoice_amount,
x_invoice_ts,x_tax_amount,x_asn_type
FROM pos_headers_v poh, po_vendors pov
WHERE poh.shipment_num = x_shipment_num AND
poh.vendor_id = pov.vendor_id AND
poh.vendor_id = x_vendor_id AND
poh.vendor_site_id = x_vendor_site_id ;
SELECT distinct poh.shipment_num,pov.vendor_name,
poh.shipped_date,
to_char(poh.shipped_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
poh.expected_receipt_date,
to_char(poh.expected_receipt_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
poh.invoice_num,poh.total_invoice_amount,
poh.invoice_date,
poh.tax_amount,poh.asn_type
INTO x_shipment_num,x_vendor_name,
x_shipped_date,
x_shipped_date_ts,
x_expected_receipt_date,
x_expected_receipt_ts,
x_invoice_num,x_total_invoice_amount,
x_invoice_date,x_tax_amount,x_asn_type
FROM POS_HEADERS_V poh,PO_VENDORS pov
WHERE poh.shipment_num = x_shipment_num AND
poh.vendor_id = pov.vendor_id AND
poh.vendor_id = x_vendor_id AND
poh.vendor_site_id = x_vendor_site_id ;
SELECT
DECODE(PRL.PO_RELEASE_ID,NULL,PH.clm_document_number,PH.clm_document_number || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUM,
ph.revision_num REVISION_NUM,
pola.line_num LINE_NUM,
pll.shipment_num SHIPMENT_NUM,
pos_get.get_item_number(rti.item_id,ood.organization_id) ITEM_NUM,
pola.item_description ITEM_DESC,
pola.unit_meas_lookup_code UOM,
pll.quantity QUANTITY_ORDERED,
rti.quantity QUANTITY_SHIPPED,
pll.quantity_received QUANTITY_RECEIVED,
NVL( HRL.LOCATION_CODE,
SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) ship_to_location_code,
ood.ORGANIZATION_CODE ORGANIZATION_CODE
FROM rcv_transactions_interface rti, rcv_headers_interface rhi ,
org_organization_definitions ood,po_releases_all prl,
po_line_locations_all pll,po_lines_all pola,po_headers_all ph,
hr_locations_all_tl hrl, hz_locations hz
WHERE rhi.header_interface_id=rti.header_interface_id and
rhi.shipment_num= p_shipment_num and
pola.po_line_id = rti.po_line_id and
nvl(prl.agent_id,ph.agent_id)=v_buyer_id and
pll.po_release_id = prl.po_release_id(+) and
pll.line_location_id=rti.po_line_location_id and
ood.organization_id = pll.ship_to_organization_id and
ph.po_header_id = rti.po_header_id and
rti.vendor_id = p_vendor_id and
rti.vendor_site_id = p_vendor_site_id and
HRL.LOCATION_ID (+) = rti.SHIP_TO_LOCATION_ID AND
HRL.LANGUAGE(+) = USERENV('LANG') AND
HZ.LOCATION_ID(+) = rti.SHIP_TO_LOCATION_ID
UNION ALL
SELECT
DECODE(PRL.PO_RELEASE_ID,NULL,PH.clm_document_number,PH.clm_document_number || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUM,
ph.revision_num REVISION_NUM,
pola.line_num LINE_NUM,
pll.shipment_num SHIPMENT_NUM,
pos_get.get_item_number(rsl.item_id,ood.organization_id) ITEM_NUM,
pola.item_description ITEM_DESC,
pola.unit_meas_lookup_code UOM,
pll.quantity QUANTITY_ORDERED,
rsl.quantity_shipped QUANTITY_SHIPPED,
pll.quantity_received QUANTITY_RECEIVED,
NVL( HRL.LOCATION_CODE,
SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) ship_to_location_code,
ood.ORGANIZATION_CODE ORGANIZATION_CODE
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh ,
org_organization_definitions ood,po_releases_all prl,
po_line_locations_all pll,po_lines_all pola,po_headers_all ph,
hr_locations_all_tl hrl,hz_locations hz
WHERE rsh.shipment_header_id=rsl.shipment_header_id and
rsh.shipment_num= p_shipment_num and
pola.po_line_id = rsl.po_line_id and
nvl(prl.agent_id,ph.agent_id)=v_buyer_id and
pll.po_release_id = prl.po_release_id(+) and
pll.line_location_id=rsl.po_line_location_id and
ood.organization_id = pll.ship_to_organization_id and
ph.po_header_id = rsl.po_header_id and
HRL.LOCATION_ID (+) = rsl.SHIP_TO_LOCATION_ID AND
HRL.LANGUAGE(+) = USERENV('LANG') AND
HZ.LOCATION_ID(+) = rsl.SHIP_TO_LOCATION_ID and
rsh.vendor_id = p_vendor_id and
rsh.vendor_site_id=p_vendor_site_id;
SELECT distinct poh.shipment_num,pov.vendor_name,
--to_char(poh.shipped_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
--to_char(poh.expected_receipt_date,fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')||' HH24:MI:SS'),
poh.shipped_date,
poh.expected_receipt_date,
poh.invoice_num,poh.total_invoice_amount,
poh.invoice_date,
poh.tax_amount,poh.asn_type,
poh.remit_to_site_id
INTO h_shipment_num,h_vendor_name,
--h_shipped_date,
--h_expected_receipt_date,
h_shipped_date_ts,
h_expected_receipt_date_ts,
h_invoice_num,h_total_invoice_amount,
h_invoice_date,
h_tax_amount,h_asn_type,
l_remit_to_site_id
FROM POS_HEADERS_V poh,PO_VENDORS pov
WHERE poh.shipment_num = x_shipment_num AND
poh.vendor_id = pov.vendor_id AND
poh.vendor_id = to_number(x_vendor_id) AND
poh.vendor_site_id = to_number(x_vendor_site_id);
SELECT pvs.VENDOR_SITE_CODE,
pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.address_line4,
pvs.city || ', ' || pvs.state || ' ' || pvs.zip,
pvs.country
INTO l_remit_to_site_code,
l_remit_to_address1,
l_remit_to_address2,
l_remit_to_address3,
l_remit_to_address4,
l_remit_to_czinfo,
l_remit_to_country
FROM PO_VENDOR_SITES_ALL pvs
WHERE pvs.vendor_site_id = l_remit_to_site_id;
select Nvl(user_id, -1)
INTO l_user_id
FROM fnd_user
WHERE user_name = x_buyer_user_name;
SELECT Nvl(FND_PROFILE.value_specific('ENABLE_TIMEZONE_CONVERSIONS', l_user_id), 'N')
INTO l_timezone_conversion
FROM dual;
select count(*) into x_num_lines from pos_headers_v
where shipment_num=x_shipment_num and
vendor_id = x_vendor_id and
vendor_site_id = x_vendor_site_id;