[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,
GT.HEADER_GDF_ATTRIBUTE18,
GT.HEADER_GDF_ATTRIBUTE28
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_2.INTERFACE_LINE_ID)
FROM RA_INTERFACE_LINES_GT GT_2
WHERE GT_2.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID);
CURSOR C_AUTOINV_TOW_ATTR IS SELECT
GT.CUSTOMER_TRX_ID,
JLEXT.HEADER_GDF_ATTRIBUTE31,
JLEXT.HEADER_GDF_ATTRIBUTE32,
JLEXT.HEADER_GDF_ATTRIBUTE33,
JLEXT.HEADER_GDF_ATTRIBUTE34,
JLEXT.HEADER_GDF_ATTRIBUTE35,
JLEXT.HEADER_GDF_ATTRIBUTE36,
JLEXT.HEADER_GDF_ATTRIBUTE37,
JLEXT.HEADER_GDF_ATTRIBUTE38,
JLEXT.HEADER_GDF_ATTRIBUTE39 --BUG 11825724
FROM RA_INTERFACE_LINES_ALL GT,
RA_CUSTOMER_TRX TRX,
JL_BR_INTERFACE_LINES_EXTS JLEXT
WHERE TRX.REQUEST_ID = P_REQUEST_ID
AND GT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND GT.ROWID = JLEXT.JL_BR_INTERFACE_LINK_ID
AND GT.INTERFACE_LINE_ID = (SELECT MIN(GT_2.INTERFACE_LINE_ID)
FROM RA_INTERFACE_LINES_GT GT_2
WHERE GT_2.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID);
CURSOR C_AUTOINV_LINE_ATTR IS SELECT
ARINT.CUSTOMER_TRX_ID,
ARINT.INTERFACE_LINE_ID,
JLEXT.LINE_GDF_ATTRIBUTE21,
JLEXT.LINE_GDF_ATTRIBUTE22,
JLEXT.LINE_GDF_ATTRIBUTE23,
JLEXT.LINE_GDF_ATTRIBUTE24,
JLEXT.LINE_GDF_ATTRIBUTE25,
JLEXT.LINE_GDF_ATTRIBUTE26,
JLEXT.LINE_GDF_ATTRIBUTE27,
JLEXT.LINE_GDF_ATTRIBUTE28,
JLEXT.LINE_GDF_ATTRIBUTE29,
JLEXT.LINE_GDF_ATTRIBUTE30,
JLEXT.LINE_GDF_ATTRIBUTE31,
JLEXT.LINE_GDF_ATTRIBUTE32,
JLEXT.LINE_GDF_ATTRIBUTE33,
JLEXT.LINE_GDF_ATTRIBUTE34,
JLEXT.LINE_GDF_ATTRIBUTE35,
JLEXT.LINE_GDF_ATTRIBUTE36,
JLEXT.LINE_GDF_ATTRIBUTE37,
JLEXT.LINE_GDF_ATTRIBUTE38,
JLEXT.LINE_GDF_ATTRIBUTE39,
JLEXT.LINE_GDF_ATTRIBUTE40,
JLEXT.LINE_GDF_ATTRIBUTE41,
JLEXT.LINE_GDF_ATTRIBUTE42,
JLEXT.LINE_GDF_ATTRIBUTE43,
JLEXT.LINE_GDF_ATTRIBUTE44,
JLEXT.LINE_GDF_ATTRIBUTE45,
JLEXT.LINE_GDF_ATTRIBUTE46,
JLEXT.LINE_GDF_ATTRIBUTE47,
JLEXT.LINE_GDF_ATTRIBUTE48,
JLEXT.LINE_GDF_ATTRIBUTE49,
JLEXT.LINE_GDF_ATTRIBUTE50
FROM JL_BR_INTERFACE_LINES_EXTS JLEXT,
RA_INTERFACE_LINES_ALL ARINT,
RA_CUSTOMER_TRX TRX
WHERE TRX.REQUEST_ID = P_REQUEST_ID
AND ARINT.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND ARINT.ROWID = JLEXT.JL_BR_INTERFACE_LINK_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
,OLD_TRX.WAGON_CODE
,OLD_TRX.FERRY_CODE
-- BUG 10184720 Added for 4 new set of Towing Vehicle attributes
,OLD_TRX.TOWING_VEH_PLATE_NUMBER2
,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE2
,OLD_TRX.TOWING_VEH_PLATE_NUMBER3
,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE3
,OLD_TRX.TOWING_VEH_PLATE_NUMBER4
,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE4
,OLD_TRX.TOWING_VEH_PLATE_NUMBER5
,OLD_TRX.TOWING_VEH_PLATE_STATE_CODE5
,OLD_TRX.SRV_EXEC_DATE --BUG 11825724 Service Execution Date
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);
CURSOR C_COPYINV_LINE_ATTR IS SELECT
NEW.CUSTOMER_TRX_ID,
NEW.CUSTOMER_TRX_LINE_ID,
OLD.INDUSTRY_ATTRIBUTE1,
OLD.INDUSTRY_ATTRIBUTE2,
OLD.INDUSTRY_ATTRIBUTE3,
OLD.INDUSTRY_ATTRIBUTE4,
OLD.INDUSTRY_ATTRIBUTE5,
OLD.INDUSTRY_ATTRIBUTE6,
OLD.INDUSTRY_ATTRIBUTE7,
OLD.INDUSTRY_ATTRIBUTE8,
OLD.INDUSTRY_ATTRIBUTE9,
OLD.INDUSTRY_ATTRIBUTE10,
OLD.INDUSTRY_ATTRIBUTE11,
OLD.INDUSTRY_ATTRIBUTE12,
OLD.INDUSTRY_ATTRIBUTE13,
OLD.INDUSTRY_ATTRIBUTE14,
OLD.INDUSTRY_ATTRIBUTE15,
OLD.INDUSTRY_ATTRIBUTE16,
OLD.INDUSTRY_ATTRIBUTE17,
OLD.INDUSTRY_ATTRIBUTE18,
OLD.INDUSTRY_ATTRIBUTE19,
OLD.INDUSTRY_ATTRIBUTE20,
OLD.INDUSTRY_ATTRIBUTE21,
OLD.INDUSTRY_ATTRIBUTE22,
OLD.INDUSTRY_ATTRIBUTE23,
OLD.INDUSTRY_ATTRIBUTE24,
OLD.INDUSTRY_ATTRIBUTE25,
OLD.INDUSTRY_ATTRIBUTE26,
OLD.INDUSTRY_ATTRIBUTE27,
OLD.INDUSTRY_ATTRIBUTE28,
OLD.INDUSTRY_ATTRIBUTE29,
OLD.INDUSTRY_ATTRIBUTE30
FROM JL_BR_CUST_TRX_LINES_EXTS OLD,
RA_CUSTOMER_TRX TRX,
RA_CUSTOMER_TRX_LINES TRXL,
(SELECT RATRX.TRX_NUMBER, RATRX.CUSTOMER_TRX_ID, RATRX.RECURRED_FROM_TRX_NUMBER, RATRX.BATCH_SOURCE_ID,
RATXL.CUSTOMER_TRX_LINE_ID, RATXL.LINE_NUMBER, RATXL.LINE_TYPE
FROM RA_CUSTOMER_TRX RATRX, RA_CUSTOMER_TRX_LINES RATXL
WHERE RATRX.REQUEST_ID = P_REQUEST_ID
AND RATRX.CUSTOMER_TRX_ID = RATXL.CUSTOMER_TRX_ID) NEW
WHERE TRX.CUSTOMER_TRX_ID = TRXL.CUSTOMER_TRX_ID
AND TRX.CUSTOMER_TRX_ID = OLD.CUSTOMER_TRX_ID
AND TRXL.CUSTOMER_TRX_LINE_ID = OLD.CUSTOMER_TRX_LINE_ID
AND NEW.RECURRED_FROM_TRX_NUMBER = TRX.TRX_NUMBER
AND NEW.BATCH_SOURCE_ID = TRX.BATCH_SOURCE_ID --Bug10354228
AND TRXL.LINE_NUMBER = NEW.LINE_NUMBER
AND TRXL.LINE_TYPE NOT IN ('TAX','FREIGHT')
AND TRXL.LINE_TYPE = NEW.LINE_TYPE;
SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') INTO L_ISSUE_AUTHORITY
FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
WHERE T.REQUEST_ID = P_REQUEST_ID
AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID
AND ROWNUM = 1;
SELECT JL_BR_EI_NUM_CODE_S.nextval INTO L_ACCESS_CODE FROM DUAL;
INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
(ELECTRONIC_INV_ACCESS_KEY_ID
,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
,WAGON_CODE
,FERRY_CODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
L_ACCESS_CODE,
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),
HEADER_GDF_ATTRIBUTE18(I),
HEADER_GDF_ATTRIBUTE28(I),
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
UPDATE JL_BR_CUSTOMER_TRX_EXTS
SET TOWING_VEH_PLATE_NUMBER2 = HEADER_GDF_ATTRIBUTE31(I),
TOWING_VEH_PLATE_STATE_CODE2 = HEADER_GDF_ATTRIBUTE32(I),
TOWING_VEH_PLATE_NUMBER3 = HEADER_GDF_ATTRIBUTE33(I),
TOWING_VEH_PLATE_STATE_CODE3 = HEADER_GDF_ATTRIBUTE34(I),
TOWING_VEH_PLATE_NUMBER4 = HEADER_GDF_ATTRIBUTE35(I),
TOWING_VEH_PLATE_STATE_CODE4 = HEADER_GDF_ATTRIBUTE36(I),
TOWING_VEH_PLATE_NUMBER5 = HEADER_GDF_ATTRIBUTE37(I),
TOWING_VEH_PLATE_STATE_CODE5 = HEADER_GDF_ATTRIBUTE38(I),
SRV_EXEC_DATE = to_date(HEADER_GDF_ATTRIBUTE39(I))
WHERE CUSTOMER_TRX_ID = AUTO_CUST_TRX_ID(I);
INSERT INTO JL_BR_CUST_TRX_LINES_EXTS
(CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,INDUSTRY_ATTRIBUTE1
,INDUSTRY_ATTRIBUTE2
,INDUSTRY_ATTRIBUTE3
,INDUSTRY_ATTRIBUTE4
,INDUSTRY_ATTRIBUTE5
,INDUSTRY_ATTRIBUTE6
,INDUSTRY_ATTRIBUTE7
,INDUSTRY_ATTRIBUTE8
,INDUSTRY_ATTRIBUTE9
,INDUSTRY_ATTRIBUTE10
,INDUSTRY_ATTRIBUTE11
,INDUSTRY_ATTRIBUTE12
,INDUSTRY_ATTRIBUTE13
,INDUSTRY_ATTRIBUTE14
,INDUSTRY_ATTRIBUTE15
,INDUSTRY_ATTRIBUTE16
,INDUSTRY_ATTRIBUTE17
,INDUSTRY_ATTRIBUTE18
,INDUSTRY_ATTRIBUTE19
,INDUSTRY_ATTRIBUTE20
,INDUSTRY_ATTRIBUTE21
,INDUSTRY_ATTRIBUTE22
,INDUSTRY_ATTRIBUTE23
,INDUSTRY_ATTRIBUTE24
,INDUSTRY_ATTRIBUTE25
,INDUSTRY_ATTRIBUTE26
,INDUSTRY_ATTRIBUTE27
,INDUSTRY_ATTRIBUTE28
,INDUSTRY_ATTRIBUTE29
,INDUSTRY_ATTRIBUTE30
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
AUTO_CUSTOMER_TRX_ID(I),
AUTO_CUSTOMER_TRX_LINE_ID(I),
LINE_GDF_ATTRIBUTE21(I),
LINE_GDF_ATTRIBUTE22(I),
LINE_GDF_ATTRIBUTE23(I),
LINE_GDF_ATTRIBUTE24(I),
LINE_GDF_ATTRIBUTE25(I),
LINE_GDF_ATTRIBUTE26(I),
LINE_GDF_ATTRIBUTE27(I),
LINE_GDF_ATTRIBUTE28(I),
LINE_GDF_ATTRIBUTE29(I),
LINE_GDF_ATTRIBUTE30(I),
LINE_GDF_ATTRIBUTE31(I),
LINE_GDF_ATTRIBUTE32(I),
LINE_GDF_ATTRIBUTE33(I),
LINE_GDF_ATTRIBUTE34(I),
LINE_GDF_ATTRIBUTE35(I),
LINE_GDF_ATTRIBUTE36(I),
LINE_GDF_ATTRIBUTE37(I),
LINE_GDF_ATTRIBUTE38(I),
LINE_GDF_ATTRIBUTE39(I),
LINE_GDF_ATTRIBUTE40(I),
LINE_GDF_ATTRIBUTE41(I),
LINE_GDF_ATTRIBUTE42(I),
LINE_GDF_ATTRIBUTE43(I),
LINE_GDF_ATTRIBUTE44(I),
LINE_GDF_ATTRIBUTE45(I),
LINE_GDF_ATTRIBUTE46(I),
LINE_GDF_ATTRIBUTE47(I),
LINE_GDF_ATTRIBUTE48(I),
LINE_GDF_ATTRIBUTE49(I),
LINE_GDF_ATTRIBUTE50(I),
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
SELECT JL_BR_EI_NUM_CODE_S.nextval INTO L_ACCESS_CODE FROM DUAL;
INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
(ELECTRONIC_INV_ACCESS_KEY_ID
,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
--BUG 10184720 Added for additional 4 sets of Towing Vehicle Attribute
,TOWING_VEH_PLATE_NUMBER2
,TOWING_VEH_PLATE_STATE_CODE2
,TOWING_VEH_PLATE_NUMBER3
,TOWING_VEH_PLATE_STATE_CODE3
,TOWING_VEH_PLATE_NUMBER4
,TOWING_VEH_PLATE_STATE_CODE4
,TOWING_VEH_PLATE_NUMBER5
,TOWING_VEH_PLATE_STATE_CODE5
,TOWING_VEH_ANTT_INSCRIPTION
,SEAL_NUMBER
,WAGON_CODE
,FERRY_CODE
,SRV_EXEC_DATE --BUG 11825724
--,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(
L_ACCESS_CODE
,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)
-- BUG 10184720 Added for additional 4 sets of Towing Vehicle Attribute
,TOWING_VEH_PLATE_NUMBER2(I)
,TOWING_VEH_PLATE_STATE_CODE2(I)
,TOWING_VEH_PLATE_NUMBER3(I)
,TOWING_VEH_PLATE_STATE_CODE3(I)
,TOWING_VEH_PLATE_NUMBER4(I)
,TOWING_VEH_PLATE_STATE_CODE4(I)
,TOWING_VEH_PLATE_NUMBER5(I)
,TOWING_VEH_PLATE_STATE_CODE5(I)
,TOWING_VEH_ANTT_INSCRIPTION(I)
,SEAL_NUMBER(I)
,WAGON_CODE(I)
,FERRY_CODE(I)
,SERVICE_EXEC_DATE(I) --BUG 11825724
--,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);
INSERT INTO JL_BR_CUST_TRX_LINES_EXTS
(CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,INDUSTRY_ATTRIBUTE1
,INDUSTRY_ATTRIBUTE2
,INDUSTRY_ATTRIBUTE3
,INDUSTRY_ATTRIBUTE4
,INDUSTRY_ATTRIBUTE5
,INDUSTRY_ATTRIBUTE6
,INDUSTRY_ATTRIBUTE7
,INDUSTRY_ATTRIBUTE8
,INDUSTRY_ATTRIBUTE9
,INDUSTRY_ATTRIBUTE10
,INDUSTRY_ATTRIBUTE11
,INDUSTRY_ATTRIBUTE12
,INDUSTRY_ATTRIBUTE13
,INDUSTRY_ATTRIBUTE14
,INDUSTRY_ATTRIBUTE15
,INDUSTRY_ATTRIBUTE16
,INDUSTRY_ATTRIBUTE17
,INDUSTRY_ATTRIBUTE18
,INDUSTRY_ATTRIBUTE19
,INDUSTRY_ATTRIBUTE20
,INDUSTRY_ATTRIBUTE21
,INDUSTRY_ATTRIBUTE22
,INDUSTRY_ATTRIBUTE23
,INDUSTRY_ATTRIBUTE24
,INDUSTRY_ATTRIBUTE25
,INDUSTRY_ATTRIBUTE26
,INDUSTRY_ATTRIBUTE27
,INDUSTRY_ATTRIBUTE28
,INDUSTRY_ATTRIBUTE29
,INDUSTRY_ATTRIBUTE30
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
COPY_CUSTOMER_TRX_ID(I)
,COPY_CUSTOMER_TRX_LINE_ID(I)
,INDUSTRY_ATTRIBUTE1(I)
,INDUSTRY_ATTRIBUTE2(I)
,INDUSTRY_ATTRIBUTE3(I)
,INDUSTRY_ATTRIBUTE4(I)
,INDUSTRY_ATTRIBUTE5(I)
,INDUSTRY_ATTRIBUTE6(I)
,INDUSTRY_ATTRIBUTE7(I)
,INDUSTRY_ATTRIBUTE8(I)
,INDUSTRY_ATTRIBUTE9(I)
,INDUSTRY_ATTRIBUTE10(I)
,INDUSTRY_ATTRIBUTE11(I)
,INDUSTRY_ATTRIBUTE12(I)
,INDUSTRY_ATTRIBUTE13(I)
,INDUSTRY_ATTRIBUTE14(I)
,INDUSTRY_ATTRIBUTE15(I)
,INDUSTRY_ATTRIBUTE16(I)
,INDUSTRY_ATTRIBUTE17(I)
,INDUSTRY_ATTRIBUTE18(I)
,INDUSTRY_ATTRIBUTE19(I)
,INDUSTRY_ATTRIBUTE20(I)
,INDUSTRY_ATTRIBUTE21(I)
,INDUSTRY_ATTRIBUTE22(I)
,INDUSTRY_ATTRIBUTE23(I)
,INDUSTRY_ATTRIBUTE24(I)
,INDUSTRY_ATTRIBUTE25(I)
,INDUSTRY_ATTRIBUTE26(I)
,INDUSTRY_ATTRIBUTE27(I)
,INDUSTRY_ATTRIBUTE28(I)
,INDUSTRY_ATTRIBUTE29(I)
,INDUSTRY_ATTRIBUTE30(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,
GT.GLOBAL_ATTRIBUTE18,
GT.GLOBAL_ATTRIBUTE28
FROM AR_TRX_HEADER_GT GT
WHERE GT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
CURSOR C_APIINV_TOW_ATTR IS SELECT
GT.CUSTOMER_TRX_ID,
T.TOWING_VEH_PLATE_NUMBER2,
T.TOWING_VEH_PLATE_STATE_CODE2,
T.TOWING_VEH_PLATE_NUMBER3,
T.TOWING_VEH_PLATE_STATE_CODE3,
T.TOWING_VEH_PLATE_NUMBER4,
T.TOWING_VEH_PLATE_STATE_CODE4,
T.TOWING_VEH_PLATE_NUMBER5,
T.TOWING_VEH_PLATE_STATE_CODE5,
T.SRV_EXEC_DATE --BUG 11825724
FROM AR_TRX_HEADER_GT GT,
JL_BR_INV_API_TRX_LINES_T T
WHERE GT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID
AND GT.TRX_HEADER_ID = T.TRX_HEADER_ID
AND T.TRX_LINE_ID IS NULL; --bug 10265440
CURSOR C_APIINV_LINE_ATTR IS SELECT
ARGT.CUSTOMER_TRX_ID,
ARGT.CUSTOMER_TRX_LINE_ID,
T.INDUSTRY_ATTRIBUTE1,
T.INDUSTRY_ATTRIBUTE2,
T.INDUSTRY_ATTRIBUTE3,
T.INDUSTRY_ATTRIBUTE4,
T.INDUSTRY_ATTRIBUTE5,
T.INDUSTRY_ATTRIBUTE6,
T.INDUSTRY_ATTRIBUTE7,
T.INDUSTRY_ATTRIBUTE8,
T.INDUSTRY_ATTRIBUTE9,
T.INDUSTRY_ATTRIBUTE10,
T.INDUSTRY_ATTRIBUTE11,
T.INDUSTRY_ATTRIBUTE12,
T.INDUSTRY_ATTRIBUTE13,
T.INDUSTRY_ATTRIBUTE14,
T.INDUSTRY_ATTRIBUTE15,
T.INDUSTRY_ATTRIBUTE16,
T.INDUSTRY_ATTRIBUTE17,
T.INDUSTRY_ATTRIBUTE18,
T.INDUSTRY_ATTRIBUTE19,
T.INDUSTRY_ATTRIBUTE20,
T.INDUSTRY_ATTRIBUTE21,
T.INDUSTRY_ATTRIBUTE22,
T.INDUSTRY_ATTRIBUTE23,
T.INDUSTRY_ATTRIBUTE24,
T.INDUSTRY_ATTRIBUTE25,
T.INDUSTRY_ATTRIBUTE26,
T.INDUSTRY_ATTRIBUTE27,
T.INDUSTRY_ATTRIBUTE28,
T.INDUSTRY_ATTRIBUTE29,
T.INDUSTRY_ATTRIBUTE30
FROM JL_BR_INV_API_TRX_LINES_T T,
AR_TRX_LINES_GT ARGT
WHERE T.TRX_LINE_ID = ARGT.TRX_LINE_ID
AND T.TRX_HEADER_ID = ARGT.TRX_HEADER_ID
AND ARGT.CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
SELECT NVL(B.GLOBAL_ATTRIBUTE5, 'N') INTO L_ISSUE_AUTHORITY
FROM RA_BATCH_SOURCES B, RA_CUSTOMER_TRX T
WHERE T.CUSTOMER_TRX_ID = CUSTOMER_TRX_ID(I)
AND B.BATCH_SOURCE_ID = T.BATCH_SOURCE_ID;
SELECT JL_BR_EI_NUM_CODE_S.nextval INTO L_ACCESS_CODE FROM DUAL;
INSERT INTO JL_BR_CUSTOMER_TRX_EXTS
(ELECTRONIC_INV_ACCESS_KEY_ID
,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
,WAGON_CODE
,FERRY_CODE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY)
VALUES(
L_ACCESS_CODE,
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),
HEADER_GDF_ATTRIBUTE18(I),
HEADER_GDF_ATTRIBUTE28(I),
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
UPDATE JL_BR_CUSTOMER_TRX_EXTS
SET TOWING_VEH_PLATE_NUMBER2 = TOWING_VEH_PLATE_NUMBER2(I),
TOWING_VEH_PLATE_STATE_CODE2 = TOWING_VEH_PLATE_STATE_CODE2(I),
TOWING_VEH_PLATE_NUMBER3 = TOWING_VEH_PLATE_NUMBER3(I),
TOWING_VEH_PLATE_STATE_CODE3 = TOWING_VEH_PLATE_STATE_CODE3(I),
TOWING_VEH_PLATE_NUMBER4 = TOWING_VEH_PLATE_NUMBER4(I),
TOWING_VEH_PLATE_STATE_CODE4 = TOWING_VEH_PLATE_STATE_CODE4(I),
TOWING_VEH_PLATE_NUMBER5 = TOWING_VEH_PLATE_NUMBER5(I),
TOWING_VEH_PLATE_STATE_CODE5 = TOWING_VEH_PLATE_STATE_CODE5(I),
SRV_EXEC_DATE = SERVICE_EXEC_DATE(I) --BUG 11825724
WHERE CUSTOMER_TRX_ID = CUST_TRX_ID(I);
INSERT INTO JL_BR_CUST_TRX_LINES_EXTS
(CUSTOMER_TRX_ID
,CUSTOMER_TRX_LINE_ID
,INDUSTRY_ATTRIBUTE1
,INDUSTRY_ATTRIBUTE2
,INDUSTRY_ATTRIBUTE3
,INDUSTRY_ATTRIBUTE4
,INDUSTRY_ATTRIBUTE5
,INDUSTRY_ATTRIBUTE6
,INDUSTRY_ATTRIBUTE7
,INDUSTRY_ATTRIBUTE8
,INDUSTRY_ATTRIBUTE9
,INDUSTRY_ATTRIBUTE10
,INDUSTRY_ATTRIBUTE11
,INDUSTRY_ATTRIBUTE12
,INDUSTRY_ATTRIBUTE13
,INDUSTRY_ATTRIBUTE14
,INDUSTRY_ATTRIBUTE15
,INDUSTRY_ATTRIBUTE16
,INDUSTRY_ATTRIBUTE17
,INDUSTRY_ATTRIBUTE18
,INDUSTRY_ATTRIBUTE19
,INDUSTRY_ATTRIBUTE20
,INDUSTRY_ATTRIBUTE21
,INDUSTRY_ATTRIBUTE22
,INDUSTRY_ATTRIBUTE23
,INDUSTRY_ATTRIBUTE24
,INDUSTRY_ATTRIBUTE25
,INDUSTRY_ATTRIBUTE26
,INDUSTRY_ATTRIBUTE27
,INDUSTRY_ATTRIBUTE28
,INDUSTRY_ATTRIBUTE29
,INDUSTRY_ATTRIBUTE30
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
)
VALUES(
CUSTOMER_TRX_ID(I),
CUSTOMER_TRX_LINE_ID(I),
INDUSTRY_ATTRIBUTE1(I),
INDUSTRY_ATTRIBUTE2(I),
INDUSTRY_ATTRIBUTE3(I),
INDUSTRY_ATTRIBUTE4(I),
INDUSTRY_ATTRIBUTE5(I),
INDUSTRY_ATTRIBUTE6(I),
INDUSTRY_ATTRIBUTE7(I),
INDUSTRY_ATTRIBUTE8(I),
INDUSTRY_ATTRIBUTE9(I),
INDUSTRY_ATTRIBUTE10(I),
INDUSTRY_ATTRIBUTE11(I),
INDUSTRY_ATTRIBUTE12(I),
INDUSTRY_ATTRIBUTE13(I),
INDUSTRY_ATTRIBUTE14(I),
INDUSTRY_ATTRIBUTE15(I),
INDUSTRY_ATTRIBUTE16(I),
INDUSTRY_ATTRIBUTE17(I),
INDUSTRY_ATTRIBUTE18(I),
INDUSTRY_ATTRIBUTE19(I),
INDUSTRY_ATTRIBUTE20(I),
INDUSTRY_ATTRIBUTE21(I),
INDUSTRY_ATTRIBUTE22(I),
INDUSTRY_ATTRIBUTE23(I),
INDUSTRY_ATTRIBUTE24(I),
INDUSTRY_ATTRIBUTE25(I),
INDUSTRY_ATTRIBUTE26(I),
INDUSTRY_ATTRIBUTE27(I),
INDUSTRY_ATTRIBUTE28(I),
INDUSTRY_ATTRIBUTE29(I),
INDUSTRY_ATTRIBUTE30(I),
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
DELETE FROM JL_BR_INV_API_TRX_LINES_T
WHERE TRX_HEADER_ID IN (SELECT TRX_HEADER_ID FROM AR_TRX_HEADER_GT
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_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;
PROCEDURE DELETE_JL_INT_EXTN_TAB (p_request_id IN NUMBER) IS
TYPE rowid_tab_T IS TABLE OF ROWID;
SELECT rowid BULK COLLECT INTO rowid_tab FROM RA_INTERFACE_LINES
WHERE REQUEST_ID = p_request_id
AND INTERFACE_STATUS = 'P';
DELETE FROM JL_BR_INTERFACE_LINES_EXTS
WHERE JL_BR_INTERFACE_LINK_ID = rowid_tab(I);
DELETE FROM JL_BR_INTERFACE_LINES_EXTS
WHERE JL_BR_INTERFACE_LINK_ID IN (SELECT rowid FROM RA_INTERFACE_LINES
WHERE INTERFACE_STATUS = 'P');
END DELETE_JL_INT_EXTN_TAB;