[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
WT.SHIP_METHOD_CODE
INTO L_SHIP_METHOD_CODE
FROM
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_DELIVERY_LEGS WDL
WHERE WTS.STOP_ID = WDL.PICK_UP_STOP_ID
AND WDA.DELIVERY_ID = C_Q2_DELIVERY_ID
AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
AND WTS.TRIP_ID = WT.TRIP_ID
AND ROWNUM = 1;
SELECT
COUNT(NVL(SUBSTRB(A.SHIP_METHOD_MEANING
,1
,35)
,L_SHIP_METHOD_CODE))
INTO L_COUNT
FROM
WSH_CARRIER_SERVICES A,
WSH_ORG_CARRIER_SERVICES B
WHERE A.SHIP_METHOD_CODE = L_SHIP_METHOD_CODE
AND A.CARRIER_SERVICE_ID = B.CARRIER_SERVICE_ID
AND B.ORGANIZATION_ID = NVL(C_DEL_ORG_ID
,P_ORGANIZATION_ID);
SELECT
NVL(SUBSTRB(A.SHIP_METHOD_MEANING
,1
,35)
,L_SHIP_METHOD_CODE)
INTO L_CARRIER
FROM
WSH_CARRIER_SERVICES A,
WSH_ORG_CARRIER_SERVICES B
WHERE A.SHIP_METHOD_CODE = L_SHIP_METHOD_CODE
AND A.CARRIER_SERVICE_ID = B.CARRIER_SERVICE_ID
AND B.ORGANIZATION_ID = NVL(C_DEL_ORG_ID
,P_ORGANIZATION_ID);
SELECT
FV.FREIGHT_TERMS
INTO L_FREIGHT_TERMS
FROM
OE_FRGHT_TERMS_ACTIVE_V FV
WHERE FV.FREIGHT_TERMS_CODE = C_FREIGHT_TERMS_CODE;
SELECT
FOB
INTO L_FOB
FROM
OE_FOBS_ACTIVE_V FV
WHERE FV.FOB_CODE = C_FOB_CODE;
SELECT
CUSTOMER_ITEM_NUMBER
INTO L_CUST_ITEM_NUMBER
FROM
MTL_CUSTOMER_ITEMS MCI
WHERE MCI.CUSTOMER_ITEM_ID = C_CUSTOMER_ITEM_ID;
SELECT
NVL(L.LINE_SET_ID
,-999),
L.LINE_ID
INTO L_LINE_SET_ID,L_LINE_ID
FROM
OE_ORDER_LINES_ALL L
WHERE L.LINE_ID = C_SRC_LINE_ID;
SELECT
SUM(WDD.REQUESTED_QUANTITY) - NVL(SUM(WDD.SHIPPED_QUANTITY)
,0)
INTO TOTAL_UNSHIPPED_QUANTITY
FROM
WSH_DELIVERY_DETAILS WDD,
OE_ORDER_LINES_ALL L
WHERE WDD.SOURCE_LINE_ID = L.LINE_ID
AND WDD.SOURCE_CODE = 'OE'
AND WDD.CONTAINER_FLAG = 'N'
AND ( ( L.LINE_SET_ID is not null
AND L.LINE_SET_ID = L_LINE_SET_ID )
OR ( L.LINE_ID = L_LINE_ID ) );
SELECT
SUM(WDD.REQUESTED_QUANTITY) - NVL(SUM(WDD.SHIPPED_QUANTITY)
,0)
INTO TOTAL_UNSHIPPED_QUANTITY
FROM
WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID = C_SRC_LINE_ID
AND WDD.SOURCE_CODE = 'OKE';
SELECT
count(*)
FROM
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_DELIVERY_DETAILS WDD
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'Y'
AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL
AND WDA.DELIVERY_ID is not null
AND WDA.DELIVERY_ID = C_Q1_DELIVERY_ID;
SELECT VENDOR_SITE_ID
FROM WSH_DELIVERY_DETAILS WDD,
RCV_TRANSACTIONS_INTERFACE RTI
WHERE WDD.SOURCE_CODE = 'RTV'
AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
AND ROWNUM = 1
UNION
SELECT VENDOR_SITE_ID
FROM WSH_DELIVERY_DETAILS WDD,
RCV_TRANSACTIONS RT
WHERE WDD.SOURCE_CODE = 'RTV'
AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID = RT.INTERFACE_SOURCE_LINE_ID
AND WDD.RELEASED_STATUS = 'C'
AND WDD.INV_INTERFACED_FLAG = 'Y'
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND ROWNUM = 1;
SELECT
PS.LOCATION_ID
INTO BILL_TO_LOC_ID
FROM
HZ_PARTY_SITES PS,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU,
OE_ORDER_LINES_ALL OLA
WHERE OLA.LINE_ID = CF_OE_LINE_ID
AND SU.SITE_USE_ID = OLA.INVOICE_TO_ORG_ID
AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID;
SELECT SHIP_TO_LOCATION_ID,
PO_SHIPMENT_LINE_ID
INTO BILL_TO_LOC_ID,
PO_SHIP_LINE_ID
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_CODE = 'RTV'
AND SOURCE_LINE_ID = F_OE_LINE_ID
AND ROWNUM = 1;
SELECT HPS.LOCATION_ID
INTO BILL_TO_LOC_ID
FROM PO_VENDOR_SITES_ALL PVS,
HZ_PARTY_SITES HPS
WHERE PVS.VENDOR_SITE_ID = BILL_TO_SITE_ID
AND PVS.PARTY_SITE_ID = HPS.PARTY_SITE_ID;
SELECT
WDD.SOURCE_LINE_ID,
OLA.SHIP_TO_CONTACT_ID,
OLA.INVOICE_TO_CONTACT_ID
INTO OE_LINE_ID,SHP_TO_CONTACT_ID,BILL_TO_CONTACT_ID
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
OE_ORDER_LINES_ALL OLA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND NVL(WDD.CONTAINER_FLAG
,'N') = 'N'
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDA.DELIVERY_ID is not null
AND WDD.SOURCE_CODE = 'OE'
AND WDD.SOURCE_LINE_ID = OLA.LINE_ID
AND ROWNUM < 2;
SELECT
WDD.SOURCE_LINE_ID
INTO OE_LINE_ID
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
OE_ORDER_LINES_ALL OLA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDA.DELIVERY_ID is not null
AND WDD.SOURCE_CODE = 'OKE'
AND ROWNUM < 2;
SELECT
PARTY.PARTY_NAME,
NVL(PARTY.PERSON_PRE_NAME_ADJUNCT
,PARTY.PERSON_TITLE) TITLE
INTO CONTACT_NAME,L_PERSON_TITLE
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CP_BILL_TO_CONTACT_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID;
SELECT
MEANING || ' ' || CONTACT_NAME
INTO CONTACT_NAME
FROM
AR_LOOKUPS
WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
AND LOOKUP_TYPE = L_LOOKUP_TYPE;
SELECT
PARTY.PARTY_NAME,
NVL(PARTY.PERSON_PRE_NAME_ADJUNCT
,PARTY.PERSON_TITLE) TITLE
INTO CONTACT_NAME,L_PERSON_TITLE
FROM
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,
HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = CP_SHIP_TO_CONTACT_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID;
SELECT
MEANING || ' ' || CONTACT_NAME
INTO CONTACT_NAME
FROM
AR_LOOKUPS
WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
AND LOOKUP_TYPE = L_LOOKUP_TYPE;
SELECT
HP.PARTY_NAME,
NVL(HP.PERSON_PRE_NAME_ADJUNCT
,HP.PERSON_TITLE) TITLE
INTO BILL_TO_CUST_NAME,L_PERSON_TITLE
FROM
HZ_PARTY_SITES PS,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTIES HP,
OE_ORDER_LINES_ALL OLA
WHERE OLA.LINE_ID = F_OE_LINE_ID
AND SU.SITE_USE_ID = OLA.INVOICE_TO_ORG_ID
AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND HP.PARTY_ID = PS.PARTY_ID;
SELECT
MEANING || ' ' || BILL_TO_CUST_NAME
INTO BILL_TO_CUST_NAME
FROM
AR_LOOKUPS
WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
AND LOOKUP_TYPE = L_LOOKUP_TYPE;
SELECT
HP.PARTY_NAME,
NVL(HP.PERSON_PRE_NAME_ADJUNCT
,HP.PERSON_TITLE) TITLE
INTO BILL_TO_CUST_NAME,L_PERSON_TITLE
FROM
WSH_DELIVERY_DETAILS WDD,
PO_VENDORS PV,
HZ_PARTIES HP
WHERE WDD.SOURCE_LINE_ID = F_OE_LINE_ID
AND WDD.SOURCE_CODE = 'RTV'
AND WDD.CUSTOMER_ID = PV.VENDOR_ID
AND PV.PARTY_ID = HP.PARTY_ID
AND ROWNUM = 1;
SELECT
MEANING || ' ' || BILL_TO_CUST_NAME
INTO BILL_TO_CUST_NAME
FROM
AR_LOOKUPS
WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
AND LOOKUP_TYPE = L_LOOKUP_TYPE;
SELECT
TAX_CODE
INTO L_LINE_TAX_CODE
FROM
OE_ORDER_LINES_ALL
WHERE LINE_ID = F_OE_LINE_ID;
SELECT
SHIP_TO_SITE_USE_ID,
CONSIGNEE_FLAG
INTO SHP_TO_SITE_USE_ID,CONS_FLAG
FROM
WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
AND SOURCE_CODE = CP_SOURCE_CODE
AND ROWNUM < 2;
SELECT
HP.PARTY_NAME,
NVL(HP.PERSON_PRE_NAME_ADJUNCT
,HP.PERSON_TITLE) TITLE
INTO SHIP_TO_CUST_NAME,L_PERSON_TITLE
FROM
HZ_PARTY_SITE_USES PSU,
HZ_PARTY_SITES PS,
HZ_PARTIES HP
WHERE PSU.PARTY_SITE_USE_ID = SHP_TO_SITE_USE_ID
AND PSU.SITE_USE_TYPE = 'PURCHASING'
AND PS.PARTY_SITE_ID = PSU.PARTY_SITE_ID
AND HP.PARTY_ID = PS.PARTY_ID;
SELECT
HP.PARTY_NAME,
NVL(HP.PERSON_PRE_NAME_ADJUNCT
,HP.PERSON_TITLE) TITLE
INTO SHIP_TO_CUST_NAME,L_PERSON_TITLE
FROM
HZ_PARTY_SITES PS,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU,
HZ_PARTIES HP
WHERE SU.SITE_USE_ID = SHP_TO_SITE_USE_ID --RTV changes
AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND HP.PARTY_ID = PS.PARTY_ID;
SELECT
MEANING || ' ' || SHIP_TO_CUST_NAME
INTO SHIP_TO_CUST_NAME
FROM
AR_LOOKUPS
WHERE LOOKUP_CODE = L_PERSON_TITLE_UP
AND LOOKUP_TYPE = L_LOOKUP_TYPE;
SELECT
SHIP_TO_SITE_USE_ID
INTO SHP_TO_SITE_USE_ID
FROM
WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
AND SOURCE_CODE = 'OE'
AND ROWNUM < 2;
SELECT
SHIP_TO_SITE_USE_ID
INTO SHP_TO_SITE_USE_ID
FROM
WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
AND SOURCE_CODE = 'OKE'
AND ROWNUM < 2;
SELECT VENDOR_SITE_ID
FROM WSH_DELIVERY_DETAILS WDD,
RCV_TRANSACTIONS_INTERFACE RTI
WHERE WDD.SOURCE_CODE = 'RTV'
AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
AND ROWNUM = 1
UNION
SELECT VENDOR_SITE_ID
FROM WSH_DELIVERY_DETAILS WDD,
RCV_TRANSACTIONS RT
WHERE WDD.SOURCE_CODE = 'RTV'
AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID = RT.INTERFACE_SOURCE_LINE_ID
AND WDD.RELEASED_STATUS = 'C'
AND WDD.INV_INTERFACED_FLAG = 'Y'
AND RT.TRANSACTION_TYPE = 'RETURN TO VENDOR'
AND ROWNUM = 1;
SELECT
PS.LOCATION_ID
INTO BILL_TO_LOC_ID
FROM
HZ_PARTY_SITES PS,
HZ_CUST_ACCT_SITES_ALL CA,
HZ_CUST_SITE_USES_ALL SU,
OE_ORDER_LINES_ALL OLA
WHERE OLA.LINE_ID = F_OE_LINE_ID
AND SU.SITE_USE_ID = OLA.INVOICE_TO_ORG_ID
AND SU.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID;
SELECT SHIP_TO_LOCATION_ID,
PO_SHIPMENT_LINE_ID
INTO BILL_TO_LOC_ID,
PO_SHIP_LINE_ID
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_CODE = 'RTV'
AND SOURCE_LINE_ID = F_OE_LINE_ID
AND ROWNUM = 1;
SELECT HPS.LOCATION_ID
INTO BILL_TO_LOC_ID
FROM PO_VENDOR_SITES_ALL PVS,
HZ_PARTY_SITES HPS
WHERE PVS.VENDOR_SITE_ID = BILL_TO_SITE_ID
AND PVS.PARTY_SITE_ID = HPS.PARTY_SITE_ID;
SELECT
WDD.SOURCE_LINE_ID,
OLA.SHIP_TO_CONTACT_ID,
OLA.INVOICE_TO_CONTACT_ID
INTO OE_LINE_ID,SHP_TO_CONTACT_ID,BILL_TO_CONTACT_ID
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
OE_ORDER_LINES_ALL OLA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND NVL(WDD.CONTAINER_FLAG
,'N') = 'N'
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDA.DELIVERY_ID is not null
AND WDD.SOURCE_CODE = 'OE'
AND WDD.SOURCE_LINE_ID = OLA.LINE_ID
AND ROWNUM < 2;
SELECT
WDD.SOURCE_LINE_ID
INTO OE_LINE_ID
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDA.DELIVERY_ID is not null
AND WDD.SOURCE_CODE = 'OKE'
AND ROWNUM < 2;
SELECT SOURCE_LINE_ID
INTO OE_LINE_ID
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDA.DELIVERY_ID is not null
AND WDD.SOURCE_CODE = 'RTV'
AND ROWNUM < 2;
SELECT
count(distinct WDD.SOURCE_CODE)
INTO L_COUNT
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDA.DELIVERY_ID IS NOT NULL
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDD.CONTAINER_FLAG = 'N'
GROUP BY
WDD.SOURCE_CODE;
SELECT
WDD.SOURCE_CODE,
WDD.DELIVERY_DETAIL_ID
INTO L_SOURCE_CODE,DEL_DTL_ID
FROM
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND WDA.DELIVERY_ID = P_DELIVERY_ID
AND WDA.DELIVERY_ID is not null
AND ROWNUM < 2;
/* SELECT
TO_CHAR(BH.ORDER_NUMBER),
RE.FULL_NAME
INTO ORDER_NUMBER,REQ_NAME
FROM
OE_ORDER_HEADERS_ALL BH,
PO_REQUISITION_HEADERS_ALL RH,
HR_EMPLOYEES RE
WHERE RH.TYPE_LOOKUP_CODE = 'INTERNAL'
AND RH.PREPARER_ID = RE.EMPLOYEE_ID
AND BH.HEADER_ID = ATTACH_ORDER_ID
AND BH.SOURCE_DOCUMENT_ID = RH.REQUISITION_HEADER_ID
AND BH.ORDER_SOURCE_ID = 10;*/
select to_char(bh.order_number),re.full_name
into order_number, req_name
from oe_order_HEADERS_ALL bh,
oe_order_lines_all ool,
po_requisition_lines_all pll,
HR_EMPLOYEES re
where pll.SOURCE_TYPE_CODE = 'INVENTORY'
AND pll.to_person_id = re.employee_id
and bh.header_id = ool.header_id
and ool.SOURCE_DOCUMENT_LINE_ID = pll.REQUISITION_LINE_ID
and ool.SOURCE_DOCUMENT_ID = pll.REQUISITION_HEADER_ID
and bh.order_source_id = 10
and ool.line_id = ATTACH_LINE_ID ;
SELECT
DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_VL
WHERE INVENTORY_ITEM_ID = ID
AND ORGANIZATION_ID = ORG_ID;
SELECT
LOC.ADDRESS1 || ', ' || LOC.ADDRESS2 || ', ' || LOC.ADDRESS3 || ', ' || LOC.ADDRESS4 || ', ' || LOC.CITY || ', ' || LOC.STATE || ', ' || LOC.COUNTRY || ', ' || LOC.POSTAL_CODE CARRIER_ADDR
FROM
WSH_ORG_CARRIER_SITES ORG_SITES,
WSH_NEW_DELIVERIES DEL,
HZ_PARTY_SITES HZ_SITES,
HZ_LOCATIONS LOC
WHERE ORG_SITES.ORGANIZATION_ID = DEL.ORGANIZATION_ID
AND HZ_SITES.PARTY_SITE_ID = ORG_SITES.CARRIER_SITE_ID
AND HZ_SITES.PARTY_ID = C_CARRIER_ID
AND ORG_SITES.ENABLED_FLAG = 'Y'
AND LOC.LOCATION_ID = HZ_SITES.LOCATION_ID
AND DEL.DELIVERY_ID = C_DELIVERY_ID;
SELECT
LOC.ADDRESS1 || ', ' || LOC.ADDRESS2 || ', ' || LOC.ADDRESS3 || ', ' || LOC.ADDRESS4 || ', ' || LOC.CITY || ', ' || LOC.STATE || ', ' || LOC.COUNTRY || ', ' || LOC.POSTAL_CODE CARRIER_ADDR
FROM
WSH_ORG_CARRIER_SITES ORG_SITES,
WSH_NEW_DELIVERIES DEL,
HZ_PARTY_SITES HZ_SITES,
HZ_LOCATIONS LOC
WHERE ORG_SITES.ORGANIZATION_ID = DEL.ORGANIZATION_ID
AND HZ_SITES.PARTY_SITE_ID = ORG_SITES.CARRIER_SITE_ID
AND HZ_SITES.PARTY_ID = C_CARRIER_ID
AND ORG_SITES.ENABLED_FLAG = 'Y'
AND LOC.LOCATION_ID = HZ_SITES.LOCATION_ID
AND DEL.DELIVERY_ID = C_DELIVERY_ID;
SELECT
WND.CARRIER_ID DEL_CARRIER_ID
FROM
WSH_NEW_DELIVERIES WND
WHERE WND.DELIVERY_ID = C_DELIVERY_ID;
SELECT
RCODES.REPORTING_CODE_CHAR_VALUE
FROM
ZX_REPORTING_TYPES_B RTYPES,
ZX_REPORT_CODES_ASSOC RCODES,
ZX_PARTY_TAX_PROFILE PTP
WHERE RCODES.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
AND RCODES.ENTITY_ID = PTP.PARTY_TAX_PROFILE_ID
AND RCODES.REPORTING_TYPE_ID = RTYPES.REPORTING_TYPE_ID
AND RTYPES.REPORTING_TYPE_CODE = 'AR-SYSTEM-PARAM-REG-NUM'
AND PTP.PARTY_TYPE_CODE = 'OU'
AND PTP.PARTY_ID = P_ORG_ID;
SELECT hr.name
INTO cp_warehouse_name
FROM hr_organization_units hr,
wsh_new_deliveries del
WHERE del.organization_id = hr.organization_id
AND del.delivery_id = p_delivery_id;
SELECT name
INTO cp_warehouse_name
FROM hr_organization_units
WHERE organization_id=p_organization_id;
SELECT loc_bill.address1,
loc_bill.address2,
loc_bill.address3,
loc_bill.address4,
loc_bill.city,
nvl(nvl(loc_bill.province,loc_bill.state),loc_bill.county),
loc_bill.postal_code,
terr_bill.territory_short_name
INTO l_address_line_1,
l_address_line_2,
l_address_line_3,
l_address_line_4,
l_town_or_city,
l_region,
l_postal_code,
l_country
FROM hz_locations loc_bill,
fnd_territories_tl terr_bill
WHERE loc_bill.country = terr_bill.territory_code(+)
AND decode(loc_bill.country,null,userenv('LANG'),terr_bill.language) = userenv('LANG')
AND loc_bill.location_id =cf_bill_to_loc;