[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') GLOBAL_ATTRIBUTE5
FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
WHERE T.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
SELECT COUNT(*) as CTR
FROM JL_BR_CUSTOMER_TRX_EXTS
WHERE ELECTRONIC_INV_STATUS IN ('2','7')
AND CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND NOT EXISTS ( SELECT 'X' FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND SELECTED_FOR_RECEIPT_BATCH_ID = -999);
SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') GLOBAL_ATTRIBUTE5
FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
WHERE T.CUSTOMER_TRX_ID = p_customer_trx_id
AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
UPDATE AR_PAYMENT_SCHEDULES_ALL
SET SELECTED_FOR_RECEIPT_BATCH_ID = -999
WHERE CUSTOMER_TRX_ID = p_customer_trx_id;
for inserting the Electronic Invoice attributes in the JL_BR_CUSTOMER_TRX_EXTS extension table*/
FUNCTION COPY_GDF_ATTRIBUTES (P_REQUEST_ID IN NUMBER, P_CALLED_FROM IN VARCHAR2) RETURN NUMBER IS
TYPE HEADER_GDF_ATTR_TYPE IS TABLE OF RA_INTERFACE_LINES_ALL.HEADER_GDF_ATTRIBUTE1%TYPE;
CURSOR C_AUTOINV_ATTR IS SELECT
GT.CUSTOMER_TRX_ID,
GT.HEADER_GDF_ATTRIBUTE19,
GT.HEADER_GDF_ATTRIBUTE20,
GT.HEADER_GDF_ATTRIBUTE21,
GT.HEADER_GDF_ATTRIBUTE22,
GT.HEADER_GDF_ATTRIBUTE23,
GT.HEADER_GDF_ATTRIBUTE24,
GT.HEADER_GDF_ATTRIBUTE25,
GT.HEADER_GDF_ATTRIBUTE26,
GT.HEADER_GDF_ATTRIBUTE27,
GT.HEADER_GDF_ATTRIBUTE29,
GT.HEADER_GDF_ATTRIBUTE30
FROM RA_INTERFACE_LINES_GT GT,
RA_CUSTOMER_TRX TRX
WHERE TRX.REQUEST_ID = P_REQUEST_ID
AND GT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND GT.INTERFACE_LINE_ID = (SELECT MIN(GT.INTERFACE_LINE_ID)
FROM RA_INTERFACE_LINES_GT GT_2
WHERE GT_2.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID) ;
CURSOR C_COPYINV_ATTR IS SELECT
NEW_TRX.CUSTOMER_TRX_ID
,OLD_TRX.LEGAL_PROCESS_CODE
,OLD_TRX.LEGAL_PROCESS_SOURCE_IND
,OLD_TRX.VEHICLE_PLATE_STATE_CODE
,OLD_TRX.VEHICLE_ANTT_INSCRIPTION
,OLD_TRX.TOWING_VEH_PLATE_NUMBER
,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE
,OLD_TRX.TOWING_VEH_ANTT_INSCRIPTION
,OLD_TRX.SEAL_NUMBER
,OLD_TRX.ELECTRONIC_INV_WEB_ADDRESS
,OLD_TRX.ELECTRONIC_INV_ACCESS_KEY
,OLD_TRX.ELECTRONIC_INV_PROTOCOL
FROM JL_BR_CUSTOMER_TRX_EXTS OLD_TRX,
(SELECT TRX_NUMBER, CUSTOMER_TRX_ID,RECURRED_FROM_TRX_NUMBER,BATCH_SOURCE_ID
FROM RA_CUSTOMER_TRX
WHERE REQUEST_ID = P_REQUEST_ID) NEW_TRX
WHERE OLD_TRX.CUSTOMER_TRX_ID = (SELECT CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX
WHERE TRX_NUMBER = NEW_TRX.RECURRED_FROM_TRX_NUMBER
AND BATCH_SOURCE_ID = NEW_TRX.BATCH_SOURCE_ID);
INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
(CUSTOMER_TRX_ID
,LEGAL_PROCESS_CODE
,LEGAL_PROCESS_SOURCE_IND
,VEHICLE_PLATE_STATE_CODE
,VEHICLE_ANTT_INSCRIPTION
,TOWING_VEH_PLATE_NUMBER
,TOWING_VEH_PLATE_STATE_CODE
,TOWING_VEH_ANTT_INSCRIPTION
,SEAL_NUMBER
,ELECTRONIC_INV_WEB_ADDRESS
,ELECTRONIC_INV_ACCESS_KEY
,ELECTRONIC_INV_PROTOCOL
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
AUTO_CUSTOMER_TRX_ID(I),
HEADER_GDF_ATTRIBUTE19(I),
HEADER_GDF_ATTRIBUTE20(I),
HEADER_GDF_ATTRIBUTE21(I),
HEADER_GDF_ATTRIBUTE22(I),
HEADER_GDF_ATTRIBUTE23(I),
HEADER_GDF_ATTRIBUTE24(I),
HEADER_GDF_ATTRIBUTE25(I),
HEADER_GDF_ATTRIBUTE26(I),
HEADER_GDF_ATTRIBUTE27(I),
HEADER_GDF_ATTRIBUTE29(I),
HEADER_GDF_ATTRIBUTE30(I),
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
(CUSTOMER_TRX_ID
,LEGAL_PROCESS_CODE
,LEGAL_PROCESS_SOURCE_IND
,VEHICLE_PLATE_STATE_CODE
,VEHICLE_ANTT_INSCRIPTION
,TOWING_VEH_PLATE_NUMBER
,TOWING_VEH_PLATE_STATE_CODE
,TOWING_VEH_ANTT_INSCRIPTION
,SEAL_NUMBER
--,ELECTRONIC_INV_WEB_ADDRESS
--,ELECTRONIC_INV_ACCESS_KEY
--,ELECTRONIC_INV_PROTOCOL
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
COPY_CUSTOMER_TRX_ID(I)
,LEGAL_PROCESS_CODE(I)
,LEGAL_PROCESS_SOURCE_INDICATOR(I)
,VEHICLE_PLATE_STATE_CODE(I)
,VEHICLE_ANTT_INSCRIPTION(I)
,TOWING_VEH_PLATE_NUMBER(I)
,TOWING_VEH_PLATE_STATE_CODE(I)
,TOWING_VEH_ANTT_INSCRIPTION(I)
,SEAL_NUMBER(I)
--,ELECTRONIC_INV_WEB_ADDRESS(I)
--,ELECTRONIC_INV_ACCESS_KEY(I)
--,ELECTRONIC_INV_PROTOCOL(I)
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,SYSDATE
,FND_GLOBAL.USER_ID);
This function will insert the Electronic Invoice attributes IN THE JL_BR_CUSTOMER_TRX_EXTS
the the extension table */
FUNCTION COPY_GDF_ATTRIBUTES_API (P_CUSTOMER_TRX_ID IN NUMBER) RETURN NUMBER IS
TYPE HEADER_GDF_ATTR_TYPE IS TABLE OF RA_INTERFACE_LINES_ALL.HEADER_GDF_ATTRIBUTE1%TYPE;
CURSOR C_APIINV_ATTR IS SELECT
GT.CUSTOMER_TRX_ID,
GT.GLOBAL_ATTRIBUTE19,
GT.GLOBAL_ATTRIBUTE20,
GT.GLOBAL_ATTRIBUTE21,
GT.GLOBAL_ATTRIBUTE22,
GT.GLOBAL_ATTRIBUTE23,
GT.GLOBAL_ATTRIBUTE24,
GT.GLOBAL_ATTRIBUTE25,
GT.GLOBAL_ATTRIBUTE26,
GT.GLOBAL_ATTRIBUTE27,
GT.GLOBAL_ATTRIBUTE29,
GT.GLOBAL_ATTRIBUTE30
FROM AR_TRX_HEADER_GT GT
WHERE GT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
(CUSTOMER_TRX_ID
,LEGAL_PROCESS_CODE
,LEGAL_PROCESS_SOURCE_IND
,VEHICLE_PLATE_STATE_CODE
,VEHICLE_ANTT_INSCRIPTION
,TOWING_VEH_PLATE_NUMBER
,TOWING_VEH_PLATE_STATE_CODE
,TOWING_VEH_ANTT_INSCRIPTION
,SEAL_NUMBER
,ELECTRONIC_INV_WEB_ADDRESS
,ELECTRONIC_INV_ACCESS_KEY
,ELECTRONIC_INV_PROTOCOL
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
CUSTOMER_TRX_ID(I),
HEADER_GDF_ATTRIBUTE19(I),
HEADER_GDF_ATTRIBUTE20(I),
HEADER_GDF_ATTRIBUTE21(I),
HEADER_GDF_ATTRIBUTE22(I),
HEADER_GDF_ATTRIBUTE23(I),
HEADER_GDF_ATTRIBUTE24(I),
HEADER_GDF_ATTRIBUTE25(I),
HEADER_GDF_ATTRIBUTE26(I),
HEADER_GDF_ATTRIBUTE27(I),
HEADER_GDF_ATTRIBUTE29(I),
HEADER_GDF_ATTRIBUTE30(I),
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
SELECT selected_for_receipt_batch_id, sum(amount_due_remaining) FROM ar_payment_schedules WHERE customer_trx_id = p_trx_id
GROUP BY customer_trx_id,selected_for_receipt_batch_id;
SELECT TYPE, DEFAULT_STATUS FROM ra_cust_trx_types WHERE cust_trx_type_id = p_type_id;
UPDATE ar_payment_schedules
SET selected_for_receipt_batch_id = NULL
WHERE selected_for_receipt_batch_id = -999 AND
customer_trx_id = p_inv_customer_trx_id;