[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER := TO_NUMBER (fnd_profile.value ('USER_ID'));
g_last_update_login NUMBER := TO_NUMBER (fnd_profile.value ('LOGIN_ID'));
SELECT po_header_id,
po_release_id,
concurrent_request_id
FROM por_feed_records
WHERE concurrent_request_id = g_conc_req_id
GROUP BY po_header_id,
po_release_id,
concurrent_request_id;
SELECT field_length
INTO l_size
FROM por_feed_field_formats
WHERE column_name = i_column
AND record_type = i_record_type
AND format_id = g_format_id
AND effective_date <= SYSDATE
AND expiration_date IS NULL;
SELECT
po_number,
release_number,
quantity,
unit_of_measure,
unit_price,
amount,
item_description,
chart_of_accounts_id,
accounting_code,
item_number,
line_cancel_flag,
shipment_cancel_flag
FROM
por_feed_records
WHERE
po_header_id = i_po_header_id
AND po_release_id = i_release_id
AND concurrent_request_id = g_conc_req_id;
SELECT column_name,
field_length,
default_value,
pad_character
FROM por_feed_field_formats
WHERE format_id = g_format_id
AND record_type = 'D'
AND effective_date <= SYSDATE
AND expiration_date IS NULL
ORDER BY start_position;
SELECT column_name,
field_length,
default_value,
pad_character
FROM por_feed_field_formats
WHERE format_id = g_format_id
AND record_type = 'H'
AND effective_date <= SYSDATE
AND expiration_date IS NULL
ORDER BY start_position;
SELECT po_number,
release_number,
card_number,
card_member_name,
card_type_lookup_code,
vendor_name,
vendor_id,
vendor_site_id,
vendor_site_code,
requester_id,
order_date,
base_currency_code,
local_currency_code,
po_header_amount,
approval_status,
control_status,
approved_date,
cancel_flag,
hold_flag
INTO l_po_number,
l_release_number,
l_card_number,
l_card_member_name,
l_card_type_lookup_code,
l_vendor_name,
l_vendor_id,
l_vendor_site_id,
l_vendor_site_code,
l_requester_id,
l_order_date,
l_base_currency_code,
l_local_currency_code,
l_po_header_amount,
l_approval_status,
l_control_status,
l_approved_date,
l_cancel_flag,
l_hold_flag
FROM por_feed_records
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND concurrent_request_id = g_conc_req_id
AND nvl(error_flag, 'N') = 'N'
AND rownum = 1; --to avoid multiple records for multiple distr
SELECT first_name || ' ' || middle_name ||
decode(middle_name, null, '', ' ') || last_name
INTO l_requester_name
FROM HR_EMPLOYEES
WHERE EMPLOYEE_ID = l_requester_id;
SELECT count(*)
INTO l_total_distr_records
FROM por_feed_records
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND (line_cancel_flag IS NULL OR line_cancel_flag = 'N')
AND (shipment_cancel_flag IS NULL OR shipment_cancel_flag = 'N')
AND concurrent_request_id = g_conc_req_id;
SELECT column_name,
field_length,
default_value,
pad_character
FROM por_feed_field_formats
WHERE format_id = g_format_id
AND record_type = 'C'
AND effective_date <= SYSDATE
AND expiration_date IS NULL
ORDER BY start_position;
SELECT po_header_amount, card_number, po_number, release_number, cancel_flag, control_status
INTO l_po_header_amount, l_card_number, l_po_number, l_release_number, l_cancel_flag, l_control_status
FROM por_feed_records
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND concurrent_request_id = g_conc_req_id
AND rownum = 1;
UPDATE por_feed_records
SET error_flag = 'Y'
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND concurrent_request_id = g_conc_req_id;
UPDATE por_feed_records
SET error_flag = 'Y'
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND concurrent_request_id = g_conc_req_id;
SELECT COUNT(*)
INTO l_total_detail_records
FROM por_feed_records
WHERE (line_cancel_fLag IS NULL OR line_cancel_flag = 'N')
AND (shipment_cancel_flag IS NULL OR shipment_cancel_flag = 'N')
AND (control_status <> 'FINALLY CLOSED' OR control_status IS NULL) --'CLOSED' ok
AND NVL(cancel_flag, 'N') = 'N'
AND nvl(error_flag, 'N') = 'N'
AND concurrent_request_id = g_conc_req_id;
SELECT MIN(order_date), MAX(order_date)
INTO l_trans_start_date, l_trans_end_date
FROM por_feed_records
WHERE NVL(error_flag, 'N') = 'N'
AND concurrent_request_id = g_conc_req_id;
SELECT card_number
FROM por_feed_records
WHERE concurrent_request_id = g_conc_req_id
GROUP BY card_number;
UPDATE por_feed_records
SET card_number = l_trim_card_number
WHERE card_number = l_card_number
AND concurrent_request_id = g_conc_req_id;
SELECT base_currency_code,
local_currency_code
INTO l_base_curr_code,
l_local_curr_code
FROM por_feed_records
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND concurrent_request_id = g_conc_req_id
AND rownum = 1;
UPDATE por_feed_records
SET po_header_amount = l_po_amount
WHERE po_header_id = rec_header.po_header_id
AND po_release_id = rec_header.po_release_id
AND concurrent_request_id = g_conc_req_id;
TYPE tab_order_date IS TABLE OF po_headers.last_update_date%TYPE;
SELECT --standard POs with catalog items
PH.SEGMENT1 PO_NUM, --header,detail
0 RELEASE_NUM, --NA for std PO
PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
0 PO_RELEASE_ID, --NA for std PO
ibycc.masked_cc_number CARD_NUM, --header
AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --for decode
AC.EMPLOYEE_ID requester_ID, --header
PV.VENDOR_NAME VENDOR_NAME, --for header, select key
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
PV.VENDOR_ID VENDOR_ID, --header
PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
PH.LAST_UPDATE_DATE ORDER_DATE, --header
GSB.CURRENCY_CODE BASE_CURR, --header
PH.CURRENCY_CODE LOCAL_CURR, --header
PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
SUBSTR(MSI.CONCATENATED_SEGMENTS,1,40) ITEM_NUM, --detail
PL.ITEM_DESCRIPTION ITEM_DESC, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED) QTY, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1,NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, -- ref
fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
PH.AUTHORIZATION_STATUS APPR_STATUS, --for order status
PH.APPROVED_DATE APPR_DATE, --for order status
PH.CLOSED_CODE CONTROL_STATUS, --for order status
PH.CANCEL_FLAG CANCEL_FLAG, --for order status
PH.USER_HOLD_FLAG hold_flag, --for order status
PL.CANCEL_FLAG line_cancel_flag, --ref
PLL.CANCEL_FLAG shipment_cancel_flag --ref
FROM
PO_HEADERS PH,
PO_LINES PL,
PO_LINE_LOCATIONS PLL,
PO_DISTRIBUTIONS PD,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
AP_CARDS AC,
AP_CARD_PROGRAMS ACP,
MTL_SYSTEM_ITEMS_KFV MSI,
GL_SETS_OF_BOOKS GSB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
iby_creditcard ibycc
WHERE
PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PH.PCARD_ID IS NOT NULL --p-card used
AND (PL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = g_inv_org_id) --only item for that inv org
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PH.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
AND PH.APPROVED_DATE IS NOT NULL
AND (PH.CANCEL_FLAG IS NULL OR PH.CANCEL_FLAG = 'N') --PO not canceled but lines/shipments may be
AND PVS.PCARD_SITE_FLAG = 'Y' --verifies supplier site uses p-card
and PH.PCARD_ID = AC.CARD_ID
AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card registered for program
AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
AND ACP.VENDOR_ID = g_card_issuer_id
AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
AND PH.LAST_UPDATE_DATE >= g_from_date_time
AND PH.LAST_UPDATE_DATE < g_to_date_time
AND ac.card_reference_id = ibycc.instrid
UNION ALL -- Standard POs with non-catalog items
SELECT
PH.SEGMENT1 PO_NUM, --header,detail
0 RELEASE_NUM, --NA for std PO
PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
0 PO_RELEASE_ID, --NA for std PO
ibycc.masked_cc_number CARD_NUM, --header
AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --for decode
AC.EMPLOYEE_ID requester_ID, --header
PV.VENDOR_NAME VENDOR_NAME, --for header, select key
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
PV.VENDOR_ID VENDOR_ID, --header
PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
PH.LAST_UPDATE_DATE ORDER_DATE, --header
GSB.CURRENCY_CODE BASE_CURR, --header
PH.CURRENCY_CODE LOCAL_CURR, --header
PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
NULL ITEM_NUM, --NA for non-catalog
PL.ITEM_DESCRIPTION ITEM_DESC, --NA for non-catalog
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED,PD.QUANTITY_ORDERED) QTY, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1,NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED,NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, -- ref
fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
PH.AUTHORIZATION_STATUS APPR_STATUS, --for order status
PH.APPROVED_DATE APPR_DATE, --for order status
PH.CLOSED_CODE CONTROL_STATUS, --for order status
PH.CANCEL_FLAG CANCEL_FLAG, --for order status
PH.USER_HOLD_FLAG hold_flag, --for order status
PL.CANCEL_FLAG line_cancel_flag, --ref
PLL.CANCEL_FLAG shipment_cancel_flag --ref
FROM
PO_HEADERS PH,
PO_LINES PL,
PO_LINE_LOCATIONS PLL,
PO_DISTRIBUTIONS PD,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
AP_CARDS AC,
AP_CARD_PROGRAMS ACP,
GL_SETS_OF_BOOKS GSB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
iby_creditcard ibycc
WHERE
PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PH.PCARD_ID IS NOT NULL --p-card is used
AND PL.ITEM_ID IS NULL --non-catalog item has no id
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PH.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
AND PH.APPROVED_DATE IS NOT NULL
AND (PH.CANCEL_FLAG IS NULL OR PH.CANCEL_FLAG = 'N') --PO not canceled but lines/shipments may be
AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
and PH.PCARD_ID = AC.CARD_ID --valid p-card
AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for program
AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
AND ACP.VENDOR_ID = g_card_issuer_id
AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
AND PH.LAST_UPDATE_DATE >= g_from_date_time
AND PH.LAST_UPDATE_DATE < g_to_date_time
AND ac.card_reference_id = ibycc.instrid
UNION ALL -- Blanket releases with non-catalog items
SELECT
PH.SEGMENT1 PO_NUM, --header, detail
PR.RELEASE_NUM RELEASE_NUM, --header
PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
PR.PO_RELEASE_ID PO_RELEASE_ID, --for get_total API
ibycc.masked_cc_number CARD_NUM, --header
AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --decode key
AC.EMPLOYEE_ID requester_ID, --header
PV.VENDOR_NAME VENDOR_NAME, --for header, select key
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
PV.VENDOR_ID VENDOR_ID, --header
PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
PR.LAST_UPDATE_DATE ORDER_DATE, --header
GSB.CURRENCY_CODE BASE_CURR, --header
PH.CURRENCY_CODE LOCAL_CURR, --header
PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
NULL ITEM_NUM, --NA for non-catalog
PL.ITEM_DESCRIPTION ITEM_DESC, --NA for non-catalog
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED) QTY, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1, NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, --ref
fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
PR.AUTHORIZATION_STATUS APPR_STATUS, --for order status
PR.APPROVED_DATE APPR_DATE, --for order status
PR.CLOSED_CODE CONTROL_STATUS, --for order status
PR.CANCEL_FLAG CANCEL_FLAG, --for order status
PR.HOLD_FLAG hold_flag, --for order status
PL.CANCEL_FLAG line_cancel_flag, --ref
PLL.CANCEL_FLAG shipment_cancel_flag --ref
FROM
PO_HEADERS PH,
PO_RELEASES PR,
PO_LINES PL,
PO_LINE_LOCATIONS PLL,
PO_DISTRIBUTIONS PD,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
AP_CARDS AC,
AP_CARD_PROGRAMS ACP,
GL_SETS_OF_BOOKS GSB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
iby_creditcard ibycc
WHERE
PH.PO_HEADER_ID = PR.PO_HEADER_ID
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PR.PCARD_ID IS NOT NULL --p-card is used
AND PL.ITEM_ID IS NULL --non-catalog item has no id
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PR.PO_RELEASE_ID = PLL.PO_RELEASE_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PR.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
AND PR.APPROVED_DATE IS NOT NULL
AND (PR.CANCEL_FLAG IS NULL OR PR.CANCEL_FLAG = 'N') --release not canceled but lines/shipments may be
AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
and PR.PCARD_ID = AC.CARD_ID --valid p-card
AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for progr
AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand
AND ACP.VENDOR_ID = g_card_issuer_id
AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
AND PR.LAST_UPDATE_DATE >= g_from_date_time
AND PR.LAST_UPDATE_DATE < g_to_date_time
AND ac.card_reference_id = ibycc.instrid
UNION ALL -- Blanket releases with catalog items
SELECT
PH.SEGMENT1 PO_NUM, --header
PR.RELEASE_NUM RELEASE_NUM, --header
PH.PO_HEADER_ID PO_HEADER_ID, --get_total API
PR.PO_RELEASE_ID PO_RELEASE_ID, --get_total API
ibycc.masked_cc_number CARD_NUM, --header
AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --decode key
AC.EMPLOYEE_ID requester_ID, --header
PV.VENDOR_NAME VENDOR_NAME, --header
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
PV.VENDOR_ID VENDOR_ID, --header
PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --header
PR.LAST_UPDATE_DATE ORDER_DATE, --header
GSB.CURRENCY_CODE FUNC_CURR, --header
PH.CURRENCY_CODE LOCAL_CURR, --header
PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
SUBSTR(MSI.CONCATENATED_SEGMENTS,1,40) ITEM_NUM, --detail
PL.ITEM_DESCRIPTION ITEM_DESC, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED) QTY, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1, NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, -- ref
fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
PR.AUTHORIZATION_STATUS APPR_STATUS, --for order status
PR.APPROVED_DATE APPR_DATE, --for order status
PR.CLOSED_CODE CONTROL_STATUS, --for order status
PR.CANCEL_FLAG CANCEL_FLAG, --for order status
PR.HOLD_FLAG hold_flag, --for order status
PL.CANCEL_FLAG line_cancel_flag, --ref
PLL.CANCEL_FLAG shipment_cancel_flag --ref
FROM
PO_HEADERS PH,
PO_RELEASES PR,
PO_LINES PL,
PO_LINE_LOCATIONS PLL,
PO_DISTRIBUTIONS PD,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
AP_CARDS AC,
AP_CARD_PROGRAMS ACP,
MTL_SYSTEM_ITEMS_KFV MSI,
GL_SETS_OF_BOOKS GSB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
iby_creditcard ibycc
WHERE
PH.PO_HEADER_ID = PR.PO_HEADER_ID
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PR.PCARD_ID IS NOT NULL --p-card is used
AND (PL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = g_inv_org_id) --only item for that inv org
AND PR.PO_RELEASE_ID = PLL.PO_RELEASE_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PR.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
AND PR.APPROVED_DATE IS NOT NULL
AND (PR.CANCEL_FLAG IS NULL OR PR.CANCEL_FLAG = 'N') --release not canceled but lines/shipments may be
AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
and PR.PCARD_ID = AC.CARD_ID --valid p-card
AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for progr
AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
AND ACP.VENDOR_ID = g_card_issuer_id
AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
AND PR.LAST_UPDATE_DATE >= g_from_date_time
AND PR.LAST_UPDATE_DATE < g_to_date_time
AND ac.card_reference_id = ibycc.instrid;
SELECT --standard POs
PH.SEGMENT1 PO_NUM, --header,detail
0 RELEASE_NUM, --NA for std PO
PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
0 PO_RELEASE_ID, --NA for std PO
ibycc.masked_cc_number CARD_NUM, --header
AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --for decode
AC.EMPLOYEE_ID requester_ID, --header
PV.VENDOR_NAME VENDOR_NAME, --for header, select key
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
PV.VENDOR_ID VENDOR_ID, --header
PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
PH.LAST_UPDATE_DATE ORDER_DATE, --header
GSB.CURRENCY_CODE BASE_CURR, --header
PH.CURRENCY_CODE LOCAL_CURR, --header
PH.AUTHORIZATION_STATUS APPR_STATUS, --for order status
PH.APPROVED_DATE APPR_DATE, --for order status
PH.CLOSED_CODE CONTROL_STATUS, --for order status
PH.CANCEL_FLAG CANCEL_FLAG, --for order status
PH.USER_HOLD_FLAG hold_flag --for order status
FROM
PO_HEADERS PH,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
AP_CARDS AC,
AP_CARD_PROGRAMS ACP,
GL_SETS_OF_BOOKS GSB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
iby_creditcard ibycc
WHERE
PH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PH.PCARD_ID IS NOT NULL --p-card is used
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PH.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
AND PH.APPROVED_DATE IS NOT NULL
AND PH.CANCEL_FLAG = 'Y'
AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
and PH.PCARD_ID = AC.CARD_ID --valid p-card
AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for program
AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
AND ACP.VENDOR_ID = g_card_issuer_id
AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
AND PH.LAST_UPDATE_DATE >= g_from_date_time
AND PH.LAST_UPDATE_DATE < g_to_date_time
AND ac.card_reference_id = ibycc.instrid
UNION ALL --blanket releases
SELECT
PH.SEGMENT1 PO_NUM, --header, detail
PR.RELEASE_NUM RELEASE_NUM, --header
PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
PR.PO_RELEASE_ID PO_RELEASE_ID, --for get_total API
ibycc.masked_cc_number CARD_NUM, --header
AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --decode key
AC.EMPLOYEE_ID requester_ID, --header
PV.VENDOR_NAME VENDOR_NAME, --for header, select key
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
PV.VENDOR_ID VENDOR_ID, --header
PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
PR.LAST_UPDATE_DATE ORDER_DATE, --header
GSB.CURRENCY_CODE BASE_CURR, --header
PH.CURRENCY_CODE LOCAL_CURR, --header
PR.AUTHORIZATION_STATUS APPR_STATUS, --for order status
PR.APPROVED_DATE APPR_DATE, --for order status
PR.CLOSED_CODE CONTROL_STATUS, --for order status
PR.CANCEL_FLAG CANCEL_FLAG, --for order status
PR.HOLD_FLAG hold_flag --for order status
FROM
PO_HEADERS PH,
PO_RELEASES PR,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
AP_CARDS AC,
AP_CARD_PROGRAMS ACP,
GL_SETS_OF_BOOKS GSB,
FINANCIALS_SYSTEM_PARAMETERS FSP,
iby_creditcard ibycc
WHERE
PH.PO_HEADER_ID = PR.PO_HEADER_ID
AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PR.PCARD_ID IS NOT NULL --p-card is used
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PR.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
AND PR.APPROVED_DATE IS NOT NULL
AND PR.CANCEL_FLAG = 'Y'
AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
and PR.PCARD_ID = AC.CARD_ID --valid p-card
AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for progr
AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand
AND ACP.VENDOR_ID = g_card_issuer_id
AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
AND PR.LAST_UPDATE_DATE >= g_from_date_time
AND PR.LAST_UPDATE_DATE < g_to_date_time
AND ac.card_reference_id = ibycc.instrid;
INSERT INTO por_feed_records (
concurrent_request_id,
po_number,
release_number,
po_header_id,
po_release_id,
card_number,
card_member_name,
card_brand_lookup_code,
card_type_lookup_code,
requester_id,
vendor_name,
vendor_site_code,
vendor_id,
vendor_site_id,
order_date,
base_currency_code,
local_currency_code,
unit_of_measure,
item_number,
item_description,
quantity,
unit_price,
amount,
chart_of_accounts_id,
accounting_code,
approval_status,
approved_date,
control_status,
cancel_flag,
hold_flag,
line_cancel_flag,
shipment_cancel_flag
)
VALUES (
g_conc_req_id,
l_po_num(i),
l_rel_num(i),
l_header_id(i),
l_release_id(i),
l_card_num(i),
l_cardmember(i),
l_card_brand(i),
l_card_type(i),
l_req_id(i),
l_vendor_name(i),
l_vendor_site_code(i),
l_vendor_id(i),
l_vendor_site_id(i),
l_order_date(i),
l_base_curr(i),
l_local_curr(i),
l_uom(i),
l_item_num(i),
l_item_desc(i),
l_qty(i),
l_unit_price(i),
l_amount(i),
l_acct_id(i),
l_acct_code(i),
l_appr_status(i),
l_appr_date(i),
l_contr_status(i),
l_cancel_flag(i),
l_hold_flag(i),
l_line_cancel_flag(i),
l_shipment_cancel_flag(i)
);
INSERT INTO por_feed_records (
concurrent_request_id,
po_number,
release_number,
po_header_id,
po_release_id,
card_number,
card_member_name,
card_brand_lookup_code,
card_type_lookup_code,
requester_id,
vendor_name,
vendor_site_code,
vendor_id,
vendor_site_id,
order_date,
base_currency_code,
local_currency_code,
approval_status,
approved_date,
control_status,
cancel_flag,
hold_flag
)
VALUES (
g_conc_req_id,
l_po_num(i),
l_rel_num(i),
l_header_id(i),
l_release_id(i),
l_card_num(i),
l_cardmember(i),
l_card_brand(i),
l_card_type(i),
l_req_id(i),
l_vendor_name(i),
l_vendor_site_code(i),
l_vendor_id(i),
l_vendor_site_id(i),
l_order_date(i),
l_base_curr(i),
l_local_curr(i),
l_appr_status(i),
l_appr_date(i),
l_contr_status(i),
l_cancel_flag(i),
l_hold_flag(i)
);
SELECT fsp.inventory_organization_id, gsb.chart_of_accounts_id, gsb.currency_code
INTO g_inv_org_id, l_org_chart_of_accounts_id, l_func_curr_code
FROM financials_system_parameters fsp,
gl_sets_of_books gsb
WHERE fsp.set_of_books_id = gsb.set_of_books_id
AND fsp.org_id = g_org_id;
INSERT INTO por_feed_history (
concurrent_request_id,
card_brand_lookup_code,
vendor_id,
vendor_site_id,
from_date_time,
to_date_time,
output_filename,
last_update_login,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES (
g_conc_req_id,
g_card_brand,
g_card_issuer_id,
g_card_issuer_site_id,
g_from_date_time,
g_to_date_time,
g_output_filename,
g_last_update_login,
sysdate,
g_last_updated_by,
sysdate,
g_created_by);
DELETE FROM por_feed_records
WHERE concurrent_request_id = g_conc_req_id;
UPDATE por_feed_history
SET status = ltrim(l_status)
WHERE concurrent_request_id = g_conc_req_id;