eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.JAI_OPEN_API_PKG dependencies on JAI_INTERFACE_LINES_ALL

Line 61: UPDATE jai_interface_lines_all

57: BEGIN
58: --At beginning, mark the reqest id to the free lines.
59: IF(NVL(pv_type,'')='ON')THEN
60:
61: UPDATE jai_interface_lines_all
62: SET request_id = GN_REQUEST_ID
63: WHERE (request_id IS NULL OR request_id = '')
64: AND import_module = GV_IMPORT_MODULE
65: AND nvl(import_status, GV_IMPORT_FAILURE) = GV_IMPORT_FAILURE

Line 79: update jai_interface_lines_all

75:
76: --At ending, wipe off the request id for all lines which marked the request id.
77: IF(NVL(pv_type,'')='OFF')THEN
78:
79: update jai_interface_lines_all
80: set request_id = ''
81: where request_id = GN_REQUEST_ID;
82:
83: END IF;--OFF

Line 105: FROM jai_interface_lines_all

101: , transaction_line_num
102: , import_status
103: , interface_line_id
104: , transaction_id
105: FROM jai_interface_lines_all
106: WHERE import_module = pv_import_module
107: AND org_id = nvl(pn_org_id, org_id)
108: AND organization_id = nvl(pn_organization_id, organization_id)
109: AND location_id = nvl(pn_location_id, location_id)

Line 172: from jai_interface_lines_all

168: delete from jai_interface_tax_lines_all
169: where interface_line_id in
170: (
171: select interface_line_id
172: from jai_interface_lines_all
173: where import_status = GV_IMPORT_SUCCESS
174: and import_process= GV_PROCESS_ACCOUNTING
175: );
176:

Line 182: from jai_interface_lines_all

178: delete from jai_interface_err_lines
179: where interface_line_id in
180: (
181: select interface_line_id
182: from jai_interface_lines_all
183: where import_status = GV_IMPORT_SUCCESS
184: and import_process= GV_PROCESS_ACCOUNTING
185: );
186:

Line 188: delete from jai_interface_lines_all

184: and import_process= GV_PROCESS_ACCOUNTING
185: );
186:
187: --Delete interface lines finally
188: delete from jai_interface_lines_all
189: where import_status = GV_IMPORT_SUCCESS
190: and import_process = GV_PROCESS_ACCOUNTING;
191:
192:

Line 198: PROCEDURE del_line_reference(pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE)

194:
195: /*
196: || After process,clear the success lines.
197: */
198: PROCEDURE del_line_reference(pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE)
199: IS
200: BEGIN
201:
202: delete from jai_interface_err_lines

Line 220: UPDATE jai_interface_lines_all

216: )
217: IS
218: BEGIN
219:
220: UPDATE jai_interface_lines_all
221: SET import_status = pv_import_status
222: , import_process = pv_import_process
223: WHERE interface_line_id = pn_interface_line_id;
224:

Line 240: UPDATE jai_interface_lines_all

236: IS
237: --PRAGMA AUTONOMOUS_TRANSACTION;
238: BEGIN
239:
240: UPDATE jai_interface_lines_all
241: SET import_status = pv_import_status
242: , import_process = pv_import_process
243: WHERE internal_trx_id = pn_trx_id;
244: --COMMIT;

Line 253: pn_chk_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,

249: || validate org_id,organization_id,location_id is suitable
250: */
251: PROCEDURE validate_org_loc_suitable(
252: pv_chk_type IN VARCHAR2,
253: pn_chk_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
254: pn_chk_organization_id IN JAI_INTERFACE_LINES_ALL.ORGANIZATION_ID%TYPE,
255: pn_chk_location_id IN JAI_INTERFACE_LINES_ALL.LOCATION_ID%TYPE,
256: xv_return_code IN OUT NOCOPY VARCHAR2,
257: xv_return_message IN OUT NOCOPY VARCHAR2)

Line 254: pn_chk_organization_id IN JAI_INTERFACE_LINES_ALL.ORGANIZATION_ID%TYPE,

250: */
251: PROCEDURE validate_org_loc_suitable(
252: pv_chk_type IN VARCHAR2,
253: pn_chk_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
254: pn_chk_organization_id IN JAI_INTERFACE_LINES_ALL.ORGANIZATION_ID%TYPE,
255: pn_chk_location_id IN JAI_INTERFACE_LINES_ALL.LOCATION_ID%TYPE,
256: xv_return_code IN OUT NOCOPY VARCHAR2,
257: xv_return_message IN OUT NOCOPY VARCHAR2)
258: IS

Line 255: pn_chk_location_id IN JAI_INTERFACE_LINES_ALL.LOCATION_ID%TYPE,

251: PROCEDURE validate_org_loc_suitable(
252: pv_chk_type IN VARCHAR2,
253: pn_chk_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
254: pn_chk_organization_id IN JAI_INTERFACE_LINES_ALL.ORGANIZATION_ID%TYPE,
255: pn_chk_location_id IN JAI_INTERFACE_LINES_ALL.LOCATION_ID%TYPE,
256: xv_return_code IN OUT NOCOPY VARCHAR2,
257: xv_return_message IN OUT NOCOPY VARCHAR2)
258: IS
259: --Cursor define here

Line 346: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,

342: /*
343: ||After validation, if errors exist, change error_flag to Y.
344: */
345: PROCEDURE update_error_flag(
346: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,
347: pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE
348: )IS
349:
350: BEGIN

Line 347: pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE

343: ||After validation, if errors exist, change error_flag to Y.
344: */
345: PROCEDURE update_error_flag(
346: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,
347: pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE
348: )IS
349:
350: BEGIN
351:

Line 352: UPDATE JAI_INTERFACE_LINES_ALL

348: )IS
349:
350: BEGIN
351:
352: UPDATE JAI_INTERFACE_LINES_ALL
353: SET ERROR_FLAG = pv_err_exist
354: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
355:
356: END update_error_flag;

Line 362: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,

358: /*
359: ||After validation, if errors exist, change error_flag to Y.
360: */
361: PROCEDURE update_trx_error_flag(
362: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,
363: pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE
364: )IS
365:
366: BEGIN

Line 363: pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE

359: ||After validation, if errors exist, change error_flag to Y.
360: */
361: PROCEDURE update_trx_error_flag(
362: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.interface_line_id%TYPE,
363: pv_err_exist IN JAI_INTERFACE_LINES_ALL.ERROR_FLAG%TYPE
364: )IS
365:
366: BEGIN
367:

Line 368: UPDATE JAI_INTERFACE_LINES_ALL

364: )IS
365:
366: BEGIN
367:
368: UPDATE JAI_INTERFACE_LINES_ALL
369: SET ERROR_FLAG = pv_err_exist
370: WHERE INTERNAL_TRX_ID = pn_internal_trx_id;
371:
372: END update_trx_error_flag;

Line 454: from jai_interface_lines_all

450:
451: lv_transaction_num VARCHAR2(30);
452: begin
453: select transaction_num INTO lv_transaction_num
454: from jai_interface_lines_all
455: where interface_line_id = cn_interface_line_id;
456:
457: RETURN lv_transaction_num;
458:

Line 598: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

594: /*
595: || Check the operating_unit,organization_id,location_id
596: */
597: PROCEDURE validate_ou_io_loc(
598: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
599: xn_err_flag IN OUT NOCOPY NUMBER
600: )IS
601: --Variable define here
602: lv_return_code VARCHAR2(10);

Line 732: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

728: /*
729: || Check the taxable event
730: */
731: PROCEDURE validate_taxable_event(
732: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
733: xn_err_flag IN OUT NOCOPY NUMBER
734: )IS
735:
736: --Cursor define here

Line 857: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

853: ||Check the taxable basis should in 'LINE_AMOUNT' OR 'ASSESSABLE_VALUE'
854: ||This validation is only for 'Standard' event.
855: */
856: PROCEDURE validate_taxable_basis(
857: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
858: xn_err_flag IN OUT NOCOPY NUMBER
859: )IS
860:
861: --Variable define here

Line 912: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

908: /*
909: ||Check the tax line is exist
910: */
911: PROCEDURE validate_tax_line_exist(
912: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
913: xn_err_flag IN OUT NOCOPY NUMBER
914: )IS
915:
916: --Cursor define here

Line 976: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

972: /*
973: ||Check the tax line should not exist in transaction tax table
974: */
975: PROCEDURE validate_trx_tax_line_exist(
976: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
977: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
978: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
979: xn_err_flag IN OUT NOCOPY NUMBER
980: )IS

Line 977: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,

973: ||Check the tax line should not exist in transaction tax table
974: */
975: PROCEDURE validate_trx_tax_line_exist(
976: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
977: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
978: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
979: xn_err_flag IN OUT NOCOPY NUMBER
980: )IS
981:

Line 978: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,

974: */
975: PROCEDURE validate_trx_tax_line_exist(
976: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
977: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
978: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
979: xn_err_flag IN OUT NOCOPY NUMBER
980: )IS
981:
982: --Cursor define here

Line 1088: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

1084: --Added by zhiwei for Bug#12639945 on 20110624 begin
1085: -------------------------------------------------------------------------------------
1086:
1087: PROCEDURE validate_trx_accted(
1088: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1089: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1090: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1091: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1092: xn_err_flag IN OUT NOCOPY NUMBER

Line 1089: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

1085: -------------------------------------------------------------------------------------
1086:
1087: PROCEDURE validate_trx_accted(
1088: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1089: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1090: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1091: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1092: xn_err_flag IN OUT NOCOPY NUMBER
1093: )

Line 1090: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

1086:
1087: PROCEDURE validate_trx_accted(
1088: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1089: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1090: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1091: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1092: xn_err_flag IN OUT NOCOPY NUMBER
1093: )
1094: IS

Line 1091: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

1087: PROCEDURE validate_trx_accted(
1088: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1089: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1090: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1091: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1092: xn_err_flag IN OUT NOCOPY NUMBER
1093: )
1094: IS
1095:

Line 1197: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

1193: ||AP:
1194: ||AR:completed.
1195: */
1196: PROCEDURE validate_trx_status(
1197: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1198: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1199: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1200: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1201: pv_trx_number IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,

Line 1198: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

1194: ||AR:completed.
1195: */
1196: PROCEDURE validate_trx_status(
1197: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1198: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1199: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1200: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1201: pv_trx_number IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
1202: xn_err_flag IN OUT NOCOPY NUMBER

Line 1199: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

1195: */
1196: PROCEDURE validate_trx_status(
1197: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1198: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1199: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1200: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1201: pv_trx_number IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
1202: xn_err_flag IN OUT NOCOPY NUMBER
1203: )IS

Line 1200: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,

1196: PROCEDURE validate_trx_status(
1197: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1198: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1199: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1200: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1201: pv_trx_number IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
1202: xn_err_flag IN OUT NOCOPY NUMBER
1203: )IS
1204:

Line 1201: pv_trx_number IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,

1197: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1198: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1199: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1200: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1201: pv_trx_number IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
1202: xn_err_flag IN OUT NOCOPY NUMBER
1203: )IS
1204:
1205: --Cursor define here

Line 1257: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;

1253: lv_ap_status VARCHAR2(30);
1254: ln_exist_flag NUMBER;
1255: lv_return_mess VARCHAR2(1000);
1256: lv_error_mess VARCHAR2(1000);
1257: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;
1258: ln_apply_amount NUMBER;
1259:
1260: --Main block
1261: BEGIN

Line 1418: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

1414: ||AP:
1415: ||AR:completed.
1416: */
1417: PROCEDURE validate_trx_period(
1418: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1419: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1420: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1421: xn_err_flag IN OUT NOCOPY NUMBER
1422: )IS

Line 1419: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

1415: ||AR:completed.
1416: */
1417: PROCEDURE validate_trx_period(
1418: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1419: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1420: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1421: xn_err_flag IN OUT NOCOPY NUMBER
1422: )IS
1423:

Line 1420: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

1416: */
1417: PROCEDURE validate_trx_period(
1418: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1419: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
1420: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1421: xn_err_flag IN OUT NOCOPY NUMBER
1422: )IS
1423:
1424: CURSOR get_ar_info_cur

Line 1662: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

1658: /*
1659: ||Check the tax line no is not null in tax lines
1660: */
1661: PROCEDURE validate_tax_line_no(
1662: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1663: pn_tax_line_id IN JAI_INTERFACE_TAX_LINES_ALL.INTERFACE_TAX_LINE_ID%TYPE,
1664: pn_tax_line_no IN JAI_INTERFACE_TAX_LINES_ALL.TAX_LINE_NO%TYPE,
1665: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1666: xn_err_flag IN OUT NOCOPY NUMBER

Line 1665: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

1661: PROCEDURE validate_tax_line_no(
1662: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
1663: pn_tax_line_id IN JAI_INTERFACE_TAX_LINES_ALL.INTERFACE_TAX_LINE_ID%TYPE,
1664: pn_tax_line_no IN JAI_INTERFACE_TAX_LINES_ALL.TAX_LINE_NO%TYPE,
1665: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1666: xn_err_flag IN OUT NOCOPY NUMBER
1667: )
1668: IS
1669: --Variable define here

Line 1715: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

1711: * 2. inclusive flag of tax should be same as the ebs system
1712: * 3. tax should in available term ,startdate and enddate
1713: */
1714: PROCEDURE validate_tax_code(
1715: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
1716: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
1717: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1718: xn_err_flag IN OUT NOCOPY NUMBER,
1719: xn_tax_id OUT NOCOPY NUMBER

Line 1717: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

1713: */
1714: PROCEDURE validate_tax_code(
1715: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
1716: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
1717: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
1718: xn_err_flag IN OUT NOCOPY NUMBER,
1719: xn_tax_id OUT NOCOPY NUMBER
1720: )
1721: IS

Line 1724: cn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,

1720: )
1721: IS
1722: --Cursor define here
1723: CURSOR get_tax_id_from_mapping_cur(
1724: cn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1725: cv_external_tax_code IN JAI_INTERFACE_TAX_LINES_ALL.EXTERNAL_TAX_CODE%TYPE,
1726: cv_taxable_event IN JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE
1727: )IS
1728: SELECT TAX_ID

Line 1726: cv_taxable_event IN JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE

1722: --Cursor define here
1723: CURSOR get_tax_id_from_mapping_cur(
1724: cn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
1725: cv_external_tax_code IN JAI_INTERFACE_TAX_LINES_ALL.EXTERNAL_TAX_CODE%TYPE,
1726: cv_taxable_event IN JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE
1727: )IS
1728: SELECT TAX_ID
1729: FROM JAI_IM_TAX_MAPPING
1730: WHERE ORG_ID = cn_org_id

Line 1985: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

1981: /*
1982: ||Check the ccid of distribution line is available
1983: */
1984: PROCEDURE validate_dist_info(
1985: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
1986: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
1987: xn_err_flag IN OUT NOCOPY NUMBER
1988: ) IS
1989:

Line 2081: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

2077: * AR NOT IN
2078: * Tax types under CUSTOMS Regime
2079: */
2080: PROCEDURE validate_tax_constrain(
2081: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
2082: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
2083: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
2084: xn_err_flag IN OUT NOCOPY NUMBER,
2085: pn_tax_id IN NUMBER

Line 2083: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

2079: */
2080: PROCEDURE validate_tax_constrain(
2081: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
2082: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
2083: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
2084: xn_err_flag IN OUT NOCOPY NUMBER,
2085: pn_tax_id IN NUMBER
2086: )
2087: IS

Line 2230: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

2226: ||--Check the tax line procedence is correct
2227: ||
2228: */
2229: PROCEDURE validate_tax_precedence(
2230: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
2231: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
2232: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
2233: xn_err_flag IN OUT NOCOPY NUMBER
2234: )

Line 2232: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

2228: */
2229: PROCEDURE validate_tax_precedence(
2230: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
2231: pr_tax_lines IN JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE,
2232: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
2233: xn_err_flag IN OUT NOCOPY NUMBER
2234: )
2235: IS
2236:

Line 2432: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

2428: ||--Check qty rate
2429: ||
2430: */
2431: PROCEDURE validate_qty_rate(
2432: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
2433: pn_tax_line_id IN JAI_INTERFACE_TAX_LINES_ALL.interface_tax_line_id%TYPE,
2434: pn_tax_id IN JAI_INTERFACE_TAX_LINES_ALL.tax_ID%TYPE,
2435: xn_err_flag IN OUT NOCOPY NUMBER
2436: )

Line 2526: FROM JAI_INTERFACE_LINES_ALL

2522: --Cursor define here
2523: CURSOR get_duplicate_info_cur
2524: IS
2525: SELECT import_module,internal_trx_id,internal_trx_line_id,COUNT(*)
2526: FROM JAI_INTERFACE_LINES_ALL
2527: WHERE request_id = GN_REQUEST_ID
2528: AND IMPORT_STATUS = GV_IMPORT_SUCCESS
2529: AND IMPORT_PROCESS = GV_PROCESS_VALIDATION
2530: group by import_module,internal_trx_id,internal_trx_line_id

Line 2536: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,

2532: order by import_module,internal_trx_id,internal_trx_line_id
2533: ;
2534:
2535: CURSOR get_dupl_lines_cur(
2536: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
2537: cn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE
2538: )
2539: IS
2540: SELECT INTERFACE_LINE_ID,

Line 2537: cn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE

2533: ;
2534:
2535: CURSOR get_dupl_lines_cur(
2536: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
2537: cn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE
2538: )
2539: IS
2540: SELECT INTERFACE_LINE_ID,
2541: transaction_num,

Line 2543: FROM JAI_INTERFACE_LINES_ALL

2539: IS
2540: SELECT INTERFACE_LINE_ID,
2541: transaction_num,
2542: transaction_line_num
2543: FROM JAI_INTERFACE_LINES_ALL
2544: WHERE INTERNAL_TRX_ID = cn_trx_id
2545: AND INTERNAL_TRX_LINE_ID = cn_trx_line_id
2546: ORDER BY 1;
2547:

Line 2632: FROM JAI_INTERFACE_LINES_ALL

2628: CURSOR get_trx_line_count_cur IS
2629: SELECT import_module import_module,
2630: internal_trx_id trx_id,
2631: COUNT(*) line_count
2632: FROM JAI_INTERFACE_LINES_ALL
2633: WHERE request_id = GN_REQUEST_ID
2634: AND IMPORT_STATUS = GV_IMPORT_SUCCESS
2635: AND IMPORT_PROCESS = GV_PROCESS_VALIDATION
2636: group by import_module,internal_trx_id

Line 2641: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE) IS

2637: order by import_module,internal_trx_id;
2638:
2639: --Get AR transaction lines count
2640: CURSOR get_ar_lines_count_cur(
2641: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE) IS
2642: SELECT COUNT(CUSTOMER_TRX_LINE_ID)
2643: FROM RA_CUSTOMER_TRX_LINES_ALL
2644: WHERE CUSTOMER_TRX_ID = cn_trx_id;
2645:

Line 2648: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE) IS

2644: WHERE CUSTOMER_TRX_ID = cn_trx_id;
2645:
2646: --Get AP transaction lines count
2647: CURSOR get_ap_lines_count_cur(
2648: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE) IS
2649: SELECT COUNT(line_number)
2650: FROM AP_INVOICE_LINES
2651: WHERE INVOICE_ID = cn_trx_id;
2652:

Line 2655: cn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

2651: WHERE INVOICE_ID = cn_trx_id;
2652:
2653: --Get error lines
2654: CURSOR get_err_lines_cur(
2655: cn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2656: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2657: )
2658: IS
2659: SELECT interface_line_id,error_flag

Line 2656: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE

2652:
2653: --Get error lines
2654: CURSOR get_err_lines_cur(
2655: cn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2656: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2657: )
2658: IS
2659: SELECT interface_line_id,error_flag
2660: FROM JAI_INTERFACE_LINES_ALL

Line 2660: FROM JAI_INTERFACE_LINES_ALL

2656: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2657: )
2658: IS
2659: SELECT interface_line_id,error_flag
2660: FROM JAI_INTERFACE_LINES_ALL
2661: WHERE IMPORT_MODULE = cn_import_module
2662: and INTERNAL_TRX_ID = cn_trx_id
2663: ORDER BY 1;
2664:

Line 2665: CURSOR chk_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2661: WHERE IMPORT_MODULE = cn_import_module
2662: and INTERNAL_TRX_ID = cn_trx_id
2663: ORDER BY 1;
2664:
2665: CURSOR chk_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2666: IS
2667: SELECT COUNT(DISTINCT TAXABLE_EVENT)
2668: FROM JAI_INTERFACE_LINES_ALL
2669: WHERE INTERNAL_TRX_ID = cn_trx_id;

Line 2668: FROM JAI_INTERFACE_LINES_ALL

2664:
2665: CURSOR chk_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2666: IS
2667: SELECT COUNT(DISTINCT TAXABLE_EVENT)
2668: FROM JAI_INTERFACE_LINES_ALL
2669: WHERE INTERNAL_TRX_ID = cn_trx_id;
2670:
2671: CURSOR get_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2672: IS

Line 2671: CURSOR get_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2667: SELECT COUNT(DISTINCT TAXABLE_EVENT)
2668: FROM JAI_INTERFACE_LINES_ALL
2669: WHERE INTERNAL_TRX_ID = cn_trx_id;
2670:
2671: CURSOR get_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2672: IS
2673: SELECT DISTINCT TAXABLE_EVENT
2674: FROM JAI_INTERFACE_LINES_ALL
2675: WHERE INTERNAL_TRX_ID = cn_trx_id;

Line 2674: FROM JAI_INTERFACE_LINES_ALL

2670:
2671: CURSOR get_taxable_event(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2672: IS
2673: SELECT DISTINCT TAXABLE_EVENT
2674: FROM JAI_INTERFACE_LINES_ALL
2675: WHERE INTERNAL_TRX_ID = cn_trx_id;
2676:
2677: CURSOR chk_vat_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2678: IS

Line 2677: CURSOR chk_vat_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2673: SELECT DISTINCT TAXABLE_EVENT
2674: FROM JAI_INTERFACE_LINES_ALL
2675: WHERE INTERNAL_TRX_ID = cn_trx_id;
2676:
2677: CURSOR chk_vat_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2678: IS
2679: SELECT
2680: COUNT(DISTINCT VAT_INVOICE_NO) vat_no_count
2681: FROM JAI_INTERFACE_LINES_ALL

Line 2681: FROM JAI_INTERFACE_LINES_ALL

2677: CURSOR chk_vat_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2678: IS
2679: SELECT
2680: COUNT(DISTINCT VAT_INVOICE_NO) vat_no_count
2681: FROM JAI_INTERFACE_LINES_ALL
2682: WHERE INTERNAL_TRX_ID = cn_trx_id;
2683: --AND VAT_INVOICE_NO IS NOT NULL;
2684:
2685: CURSOR chk_exc_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

Line 2685: CURSOR chk_exc_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2681: FROM JAI_INTERFACE_LINES_ALL
2682: WHERE INTERNAL_TRX_ID = cn_trx_id;
2683: --AND VAT_INVOICE_NO IS NOT NULL;
2684:
2685: CURSOR chk_exc_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2686: IS
2687: SELECT
2688: COUNT(DISTINCT EXCISE_INVOICE_NO) exc_no_count
2689: FROM JAI_INTERFACE_LINES_ALL

Line 2689: FROM JAI_INTERFACE_LINES_ALL

2685: CURSOR chk_exc_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2686: IS
2687: SELECT
2688: COUNT(DISTINCT EXCISE_INVOICE_NO) exc_no_count
2689: FROM JAI_INTERFACE_LINES_ALL
2690: WHERE INTERNAL_TRX_ID = cn_trx_id;
2691: --AND EXCISE_INVOICE_NO IS NOT NULL;
2692:
2693: CURSOR get_vat_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

Line 2693: CURSOR get_vat_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2689: FROM JAI_INTERFACE_LINES_ALL
2690: WHERE INTERNAL_TRX_ID = cn_trx_id;
2691: --AND EXCISE_INVOICE_NO IS NOT NULL;
2692:
2693: CURSOR get_vat_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2694: IS
2695: SELECT DISTINCT vat_invoice_no
2696: FROM JAI_INTERFACE_LINES_ALL
2697: WHERE INTERNAL_TRX_ID = cn_trx_id;

Line 2696: FROM JAI_INTERFACE_LINES_ALL

2692:
2693: CURSOR get_vat_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2694: IS
2695: SELECT DISTINCT vat_invoice_no
2696: FROM JAI_INTERFACE_LINES_ALL
2697: WHERE INTERNAL_TRX_ID = cn_trx_id;
2698:
2699: CURSOR get_excise_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2700: IS

Line 2699: CURSOR get_excise_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2695: SELECT DISTINCT vat_invoice_no
2696: FROM JAI_INTERFACE_LINES_ALL
2697: WHERE INTERNAL_TRX_ID = cn_trx_id;
2698:
2699: CURSOR get_excise_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2700: IS
2701: SELECT DISTINCT excise_invoice_no
2702: FROM JAI_INTERFACE_LINES_ALL
2703: WHERE INTERNAL_TRX_ID = cn_trx_id;

Line 2702: FROM JAI_INTERFACE_LINES_ALL

2698:
2699: CURSOR get_excise_no_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2700: IS
2701: SELECT DISTINCT excise_invoice_no
2702: FROM JAI_INTERFACE_LINES_ALL
2703: WHERE INTERNAL_TRX_ID = cn_trx_id;
2704:
2705: CURSOR get_vat_tax_exists_cur(cn_trx_id IN NUMBER) IS
2706: SELECT 'Y'

Line 2711: jai_interface_lines_all jila

2707: FROM dual
2708: WHERE EXISTS (SELECT 1
2709: FROM jai_cmn_taxes_all jcta,
2710: jai_interface_tax_lines_all jitla,
2711: jai_interface_lines_all jila
2712: WHERE jitla.tax_id = jcta.tax_id
2713: AND jila.interface_line_id = jitla.interface_line_id
2714: AND jila.internal_trx_id = cn_trx_id
2715: AND upper(jcta.tax_type) IN

Line 2729: jai_interface_lines_all jila

2725: FROM dual
2726: WHERE EXISTS (SELECT 1
2727: FROM jai_cmn_taxes_all jcta,
2728: jai_interface_tax_lines_all jitla,
2729: jai_interface_lines_all jila
2730: WHERE jitla.tax_id = jcta.tax_id
2731: AND jila.interface_line_id = jitla.interface_line_id
2732: AND jila.internal_trx_id = cn_trx_id
2733: AND upper(jcta.tax_type) IN ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS'));

Line 2736: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

2732: AND jila.internal_trx_id = cn_trx_id
2733: AND upper(jcta.tax_type) IN ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS'));
2734:
2735: CURSOR get_lines_count(
2736: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2737: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2738: )
2739: IS
2740: SELECT COUNT(1)

Line 2737: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE

2733: AND upper(jcta.tax_type) IN ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS'));
2734:
2735: CURSOR get_lines_count(
2736: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2737: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2738: )
2739: IS
2740: SELECT COUNT(1)
2741: FROM JAI_INTERFACE_LINES_ALL

Line 2741: FROM JAI_INTERFACE_LINES_ALL

2737: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2738: )
2739: IS
2740: SELECT COUNT(1)
2741: FROM JAI_INTERFACE_LINES_ALL
2742: WHERE internal_trx_id = cn_trx_id
2743: AND import_module = cv_import_module
2744: AND request_id = GN_REQUEST_ID;
2745:

Line 2749: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

2745:
2746: --Added by zhiwei for Bug#12630077 on 20110608 begin
2747: -----------------------------------------------------------------------------------------------
2748: CURSOR get_four_comb_for_parent(
2749: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2750: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2751: )
2752: IS
2753: SELECT distinct

Line 2750: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE

2746: --Added by zhiwei for Bug#12630077 on 20110608 begin
2747: -----------------------------------------------------------------------------------------------
2748: CURSOR get_four_comb_for_parent(
2749: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2750: cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE
2751: )
2752: IS
2753: SELECT distinct
2754: party_id,

Line 2758: FROM JAI_INTERFACE_LINES_ALL

2754: party_id,
2755: party_site_id,
2756: import_module,
2757: transaction_num
2758: FROM JAI_INTERFACE_LINES_ALL
2759: WHERE internal_trx_id = cn_trx_id
2760: AND import_module = cv_import_module
2761: AND request_id = GN_REQUEST_ID;
2762:

Line 2764: lv_import_module jai_interface_lines_all.import_module%type;

2760: AND import_module = cv_import_module
2761: AND request_id = GN_REQUEST_ID;
2762:
2763: ln_lines_count_parent number;
2764: lv_import_module jai_interface_lines_all.import_module%type;
2765: ln_party_id jai_interface_lines_all.party_id%type;
2766: ln_party_site_id jai_interface_lines_all.party_site_id%type;
2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;
2768:

Line 2765: ln_party_id jai_interface_lines_all.party_id%type;

2761: AND request_id = GN_REQUEST_ID;
2762:
2763: ln_lines_count_parent number;
2764: lv_import_module jai_interface_lines_all.import_module%type;
2765: ln_party_id jai_interface_lines_all.party_id%type;
2766: ln_party_site_id jai_interface_lines_all.party_site_id%type;
2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;
2768:
2769: CURSOR get_parent_lines_count(

Line 2766: ln_party_site_id jai_interface_lines_all.party_site_id%type;

2762:
2763: ln_lines_count_parent number;
2764: lv_import_module jai_interface_lines_all.import_module%type;
2765: ln_party_id jai_interface_lines_all.party_id%type;
2766: ln_party_site_id jai_interface_lines_all.party_site_id%type;
2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;
2768:
2769: CURSOR get_parent_lines_count(
2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

Line 2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;

2763: ln_lines_count_parent number;
2764: lv_import_module jai_interface_lines_all.import_module%type;
2765: ln_party_id jai_interface_lines_all.party_id%type;
2766: ln_party_site_id jai_interface_lines_all.party_site_id%type;
2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;
2768:
2769: CURSOR get_parent_lines_count(
2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2771: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

Line 2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

2766: ln_party_site_id jai_interface_lines_all.party_site_id%type;
2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;
2768:
2769: CURSOR get_parent_lines_count(
2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2771: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
2772: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
2773: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE
2774: )

Line 2771: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

2767: lv_transaction_num jai_interface_lines_all.transaction_num%type;
2768:
2769: CURSOR get_parent_lines_count(
2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2771: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
2772: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
2773: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE
2774: )
2775: IS

Line 2772: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,

2768:
2769: CURSOR get_parent_lines_count(
2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2771: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
2772: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
2773: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE
2774: )
2775: IS
2776: SELECT COUNT(1)

Line 2773: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE

2769: CURSOR get_parent_lines_count(
2770: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
2771: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
2772: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
2773: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE
2774: )
2775: IS
2776: SELECT COUNT(1)
2777: FROM JAI_INTERFACE_LINES_ALL

Line 2777: FROM JAI_INTERFACE_LINES_ALL

2773: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE
2774: )
2775: IS
2776: SELECT COUNT(1)
2777: FROM JAI_INTERFACE_LINES_ALL
2778: WHERE transaction_num = cv_transaction_num
2779: AND import_module = cv_import_module
2780: AND party_id = cn_party_id
2781: AND party_site_id = cn_party_site_id

Line 2794: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;

2790: lv_return_mess VARCHAR2(1000);
2791: lv_error_mess VARCHAR2(1000);
2792: lv_return_code VARCHAR2(20);
2793: ln_event_type NUMBER;
2794: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;
2795: ln_vat_no_count NUMBER;
2796: ln_excise_no_count NUMBER;
2797: ln_diff_tax NUMBER;
2798: lv_error_tax_inv VARCHAR2(20);

Line 2799: lv_vat_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;

2795: ln_vat_no_count NUMBER;
2796: ln_excise_no_count NUMBER;
2797: ln_diff_tax NUMBER;
2798: lv_error_tax_inv VARCHAR2(20);
2799: lv_vat_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;
2800: lv_excise_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
2801: ln_error_flag NUMBER;
2802: lv_error_type VARCHAR2(20);
2803: ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;

Line 2800: lv_excise_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;

2796: ln_excise_no_count NUMBER;
2797: ln_diff_tax NUMBER;
2798: lv_error_tax_inv VARCHAR2(20);
2799: lv_vat_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;
2800: lv_excise_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
2801: ln_error_flag NUMBER;
2802: lv_error_type VARCHAR2(20);
2803: ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
2804: lv_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;

Line 2815: CURSOR chk_st_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

2811: lv_excise_tax_exist VARCHAR2(1);
2812: --Main block
2813:
2814: /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
2815: CURSOR chk_st_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2816: IS
2817: SELECT
2818: COUNT(DISTINCT st_inv_number) st_inv_count
2819: FROM JAI_INTERFACE_LINES_ALL

Line 2819: FROM JAI_INTERFACE_LINES_ALL

2815: CURSOR chk_st_inv_no(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
2816: IS
2817: SELECT
2818: COUNT(DISTINCT st_inv_number) st_inv_count
2819: FROM JAI_INTERFACE_LINES_ALL
2820: WHERE INTERNAL_TRX_ID = cn_trx_id;
2821:
2822: CURSOR get_service_tax_exists_cur(cn_trx_id IN NUMBER) IS
2823: SELECT 'Y'

Line 2828: jai_interface_lines_all jila

2824: FROM dual
2825: WHERE EXISTS (SELECT 1
2826: FROM jai_cmn_taxes_all jcta,
2827: jai_interface_tax_lines_all jitla,
2828: jai_interface_lines_all jila
2829: WHERE jitla.tax_id = jcta.tax_id
2830: AND jila.interface_line_id = jitla.interface_line_id
2831: AND jila.internal_trx_id = cn_trx_id
2832: AND upper(jcta.tax_type) IN

Line 3391: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

3387: * Check if AR/AP transactions are created by importing or manuall created in the form.
3388: * If it is created from the form, error message will be logged.
3389: */
3390: PROCEDURE validate_import_trx(
3391: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
3392: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3394: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3395: xn_err_flag IN OUT NOCOPY NUMBER

Line 3392: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

3388: * If it is created from the form, error message will be logged.
3389: */
3390: PROCEDURE validate_import_trx(
3391: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
3392: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3394: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3395: xn_err_flag IN OUT NOCOPY NUMBER
3396: ) IS

Line 3393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,

3389: */
3390: PROCEDURE validate_import_trx(
3391: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
3392: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3394: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3395: xn_err_flag IN OUT NOCOPY NUMBER
3396: ) IS
3397:

Line 3394: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,

3390: PROCEDURE validate_import_trx(
3391: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
3392: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3394: pn_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3395: xn_err_flag IN OUT NOCOPY NUMBER
3396: ) IS
3397:
3398: --Variable define here

Line 3480: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

3476: /*
3477: ||Check the trx_id get by trx num and trx line num should be same as the trx id is gived by customer
3478: */
3479: PROCEDURE validate_trx_id_equals(
3480: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
3481: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
3482: xn_err_flag IN OUT NOCOPY NUMBER
3483: )IS
3484:

Line 3481: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

3477: ||Check the trx_id get by trx num and trx line num should be same as the trx id is gived by customer
3478: */
3479: PROCEDURE validate_trx_id_equals(
3480: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
3481: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
3482: xn_err_flag IN OUT NOCOPY NUMBER
3483: )IS
3484:
3485: --Variable define here

Line 3806: pn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

3802: /*
3803: || Get Default Tax Category for AP/AR
3804: */
3805: PROCEDURE get_default_tax_cate_id(
3806: pn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3807: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3808: pn_internal_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3809: xn_tax_category_id OUT NOCOPY NUMBER
3810: )IS

Line 3807: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,

3803: || Get Default Tax Category for AP/AR
3804: */
3805: PROCEDURE get_default_tax_cate_id(
3806: pn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3807: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3808: pn_internal_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3809: xn_tax_category_id OUT NOCOPY NUMBER
3810: )IS
3811: --Cursor define here

Line 3808: pn_internal_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,

3804: */
3805: PROCEDURE get_default_tax_cate_id(
3806: pn_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
3807: pn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
3808: pn_internal_trx_line_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE,
3809: xn_tax_category_id OUT NOCOPY NUMBER
3810: )IS
3811: --Cursor define here
3812: CURSOR bind_cur(cn_customer_trx_id IN RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE)

Line 4001: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

3997: /*
3998: || Get trx_id and trx_line_id by trx_num and trx_line_num
3999: */
4000: PROCEDURE get_trx_id_info(
4001: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,
4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

Line 4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,

3998: || Get trx_id and trx_line_id by trx_num and trx_line_num
3999: */
4000: PROCEDURE get_trx_id_info(
4001: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,
4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4006: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,

Line 4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,

3999: */
4000: PROCEDURE get_trx_id_info(
4001: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,
4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4006: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4007: xn_trx_id OUT NOCOPY NUMBER,

Line 4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

4000: PROCEDURE get_trx_id_info(
4001: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,
4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4006: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4007: xn_trx_id OUT NOCOPY NUMBER,
4008: xn_trx_line_id OUT NOCOPY NUMBER,

Line 4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

4001: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,
4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4006: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4007: xn_trx_id OUT NOCOPY NUMBER,
4008: xn_trx_line_id OUT NOCOPY NUMBER,
4009: xn_err_flag IN OUT NOCOPY NUMBER

Line 4006: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,

4002: pv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
4003: pn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE,
4004: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4005: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4006: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4007: xn_trx_id OUT NOCOPY NUMBER,
4008: xn_trx_line_id OUT NOCOPY NUMBER,
4009: xn_err_flag IN OUT NOCOPY NUMBER
4010: )

Line 4064: ln_trx_id JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE;

4060: rec_ar_trx_info get_trx_info_ar_cur%ROWTYPE;
4061: rec_ap_trx_info get_trx_info_ap_cur%ROWTYPE;
4062: lv_return_mess VARCHAR2(1000);
4063: lv_error_mess VARCHAR2(1000);
4064: ln_trx_id JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE;
4065: ln_trx_line_id JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
4066:
4067: --Main block
4068: BEGIN

Line 4065: ln_trx_line_id JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;

4061: rec_ap_trx_info get_trx_info_ap_cur%ROWTYPE;
4062: lv_return_mess VARCHAR2(1000);
4063: lv_error_mess VARCHAR2(1000);
4064: ln_trx_id JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE;
4065: ln_trx_line_id JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
4066:
4067: --Main block
4068: BEGIN
4069:

Line 4149: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

4145: /*
4146: ||Check the party and party site is available.
4147: */
4148: PROCEDURE validate_party_info(
4149: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4150: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4151: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4152: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4153: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,

Line 4150: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

4146: ||Check the party and party site is available.
4147: */
4148: PROCEDURE validate_party_info(
4149: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4150: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4151: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4152: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4153: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
4154: xn_err_flag IN OUT NOCOPY NUMBER

Line 4151: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

4147: */
4148: PROCEDURE validate_party_info(
4149: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4150: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4151: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4152: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4153: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
4154: xn_err_flag IN OUT NOCOPY NUMBER
4155: )IS

Line 4152: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,

4148: PROCEDURE validate_party_info(
4149: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4150: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4151: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4152: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4153: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
4154: xn_err_flag IN OUT NOCOPY NUMBER
4155: )IS
4156:

Line 4153: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,

4149: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4150: pv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
4151: pn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4152: pn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
4153: pn_org_id IN JAI_INTERFACE_LINES_ALL.ORG_ID%TYPE,
4154: xn_err_flag IN OUT NOCOPY NUMBER
4155: )IS
4156:
4157: CURSOR get_ar_party_cur IS

Line 4171: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

4167: AND cuse.site_use_code = 'BILL_TO'
4168: AND cuse.cust_acct_site_id = jcca.address_id;
4169:
4170: CURSOR chk_ar_party_combination_cur(
4171: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4172: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE) IS
4173: SELECT count(cuse.site_use_id)
4174: FROM hz_cust_site_uses_all cuse
4175: , jai_cmn_cus_addresses jcca

Line 4172: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE) IS

4168: AND cuse.cust_acct_site_id = jcca.address_id;
4169:
4170: CURSOR chk_ar_party_combination_cur(
4171: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4172: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE) IS
4173: SELECT count(cuse.site_use_id)
4174: FROM hz_cust_site_uses_all cuse
4175: , jai_cmn_cus_addresses jcca
4176: WHERE cuse.site_use_id = pn_party_site_id

Line 4192: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

4188: FROM jai_cmn_vendor_sites
4189: WHERE vendor_site_id = pn_party_site_id;
4190:
4191: CURSOR chk_ap_party_combination_cur(
4192: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4193: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE) IS
4194: SELECT COUNT(vendor_id)
4195: FROM jai_cmn_vendor_sites
4196: WHERE vendor_id = cn_party_id

Line 4193: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE) IS

4189: WHERE vendor_site_id = pn_party_site_id;
4190:
4191: CURSOR chk_ap_party_combination_cur(
4192: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
4193: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE) IS
4194: SELECT COUNT(vendor_id)
4195: FROM jai_cmn_vendor_sites
4196: WHERE vendor_id = cn_party_id
4197: AND vendor_site_id = cn_party_site_id;

Line 4427: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

4423: ||Check the service type for AP, if service type code is not null then vaidate it is available.
4424: || else find the default service type code and populate.
4425: */
4426: PROCEDURE validate_service_type(
4427: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4428: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
4429: pv_service_type_code IN JAI_INTERFACE_LINES_ALL.SERVICE_TYPE_CODE%TYPE,
4430: xn_err_flag IN OUT NOCOPY NUMBER
4431: )IS

Line 4428: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,

4424: || else find the default service type code and populate.
4425: */
4426: PROCEDURE validate_service_type(
4427: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4428: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
4429: pv_service_type_code IN JAI_INTERFACE_LINES_ALL.SERVICE_TYPE_CODE%TYPE,
4430: xn_err_flag IN OUT NOCOPY NUMBER
4431: )IS
4432:

Line 4429: pv_service_type_code IN JAI_INTERFACE_LINES_ALL.SERVICE_TYPE_CODE%TYPE,

4425: */
4426: PROCEDURE validate_service_type(
4427: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
4428: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
4429: pv_service_type_code IN JAI_INTERFACE_LINES_ALL.SERVICE_TYPE_CODE%TYPE,
4430: xn_err_flag IN OUT NOCOPY NUMBER
4431: )IS
4432:
4433: --Cursor define here

Line 4497: FROM JAI_INTERFACE_LINES_ALL

4493: CURSOR get_tax_category_cur
4494: IS
4495: SELECT
4496: TAX_CATEGORY_ID
4497: FROM JAI_INTERFACE_LINES_ALL
4498: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
4499:
4500:
4501: CURSOR chk_tax_category_cur(cn_tax_category_id JAI_CMN_TAX_CTG_LINES.tax_category_id%type)

Line 4630: update jai_interface_lines_all

4626: pv_fnd_log => lv_return_mess
4627: );
4628: ELSE
4629: --Update to interface line.
4630: update jai_interface_lines_all
4631: set service_type_code = lv_serv_type_code
4632: where interface_line_id = pn_interface_line_id;
4633:
4634: END IF;

Line 4665: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

4661: /*
4662: ||Check tax lines
4663: */
4664: PROCEDURE validate_tax_line(
4665: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
4666: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
4667: xn_err_flag IN OUT NOCOPY NUMBER
4668: )IS
4669:

Line 4666: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,

4662: ||Check tax lines
4663: */
4664: PROCEDURE validate_tax_line(
4665: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
4666: pn_trx_id IN JAI_INTERFACE_LINES_ALL.TRANSACTION_ID%TYPE,
4667: xn_err_flag IN OUT NOCOPY NUMBER
4668: )IS
4669:
4670: --Cursor define here

Line 4678: CURSOR chk_category_id_cur(cn_category_id IN JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE)

4674: FROM JAI_INTERFACE_TAX_LINES_ALL
4675: WHERE INTERFACE_LINE_ID = pr_interface_lines.INTERFACE_LINE_ID
4676: ORDER BY INTERFACE_TAX_LINE_ID;
4677:
4678: CURSOR chk_category_id_cur(cn_category_id IN JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE)
4679: IS
4680: SELECT tax_category_id
4681: FROM JAI_CMN_TAX_CTGS_ALL
4682: WHERE tax_category_id = cn_category_id;

Line 4699: ln_tax_category_id JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE;

4695: ln_tax_id JAI_INTERFACE_TAX_LINES_ALL.TAX_ID%TYPE;
4696: rec_tax_line JAI_INTERFACE_TAX_LINES_ALL%ROWTYPE;
4697: ln_rec_percent NUMBER;
4698: ln_func_tax_amount NUMBER;
4699: ln_tax_category_id JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE;
4700: ln_tax_exist_flag NUMBER;
4701:
4702: ln_not_ebs_code NUMBER;
4703:

Line 4844: UPDATE JAI_INTERFACE_LINES_ALL

4840: pv_fnd_log => lv_return_mess
4841: );
4842: ELSE
4843: --Update the tax category id to interface line .
4844: UPDATE JAI_INTERFACE_LINES_ALL
4845: SET tax_category_id = ln_tax_category_id --10141--
4846: where interface_line_id = pr_interface_lines.interface_line_id;
4847: END IF;
4848: END IF;

Line 4876: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,

4872: /*
4873: || Check the import module should be AR or AP
4874: */
4875: PROCEDURE validate_import_module(
4876: pr_interface_lines IN JAI_INTERFACE_LINES_ALL%ROWTYPE,
4877: xn_err_flag IN OUT NOCOPY NUMBER
4878: )
4879: IS
4880:

Line 4927: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE

4923: /*
4924: || Validation process
4925: */
4926: PROCEDURE validate_process(
4927: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE
4928: )
4929: IS
4930:
4931: --Cursor define here

Line 4936: FROM JAI_INTERFACE_LINES_ALL

4932: CURSOR get_interface_line_cur
4933: IS
4934: SELECT
4935: *
4936: FROM JAI_INTERFACE_LINES_ALL
4937: WHERE INTERFACE_LINE_ID = pn_interface_line_id
4938: AND IMPORT_STATUS = GV_IMPORT_SUCCESS
4939: AND IMPORT_PROCESS = GV_PROCESS_INITIALIZE
4940: ORDER BY 1;

Line 4946: rec_interface_line JAI_INTERFACE_LINES_ALL%ROWTYPE;

4942: --Variable define here
4943: lv_return_code VARCHAR2(30);
4944: lv_return_mess VARCHAR2(500);
4945: ln_err_flag NUMBER;
4946: rec_interface_line JAI_INTERFACE_LINES_ALL%ROWTYPE;
4947: ln_internal_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
4948: ln_internal_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
4949: ln_phase NUMBER;
4950:

Line 4947: ln_internal_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;

4943: lv_return_code VARCHAR2(30);
4944: lv_return_mess VARCHAR2(500);
4945: ln_err_flag NUMBER;
4946: rec_interface_line JAI_INTERFACE_LINES_ALL%ROWTYPE;
4947: ln_internal_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
4948: ln_internal_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
4949: ln_phase NUMBER;
4950:
4951: --Main block

Line 4948: ln_internal_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;

4944: lv_return_mess VARCHAR2(500);
4945: ln_err_flag NUMBER;
4946: rec_interface_line JAI_INTERFACE_LINES_ALL%ROWTYPE;
4947: ln_internal_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
4948: ln_internal_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
4949: ln_phase NUMBER;
4950:
4951: --Main block
4952: BEGIN

Line 5114: UPDATE JAI_INTERFACE_LINES_ALL

5110:
5111: ln_phase := 13.1;
5112:
5113: /*
5114: UPDATE JAI_INTERFACE_LINES_ALL
5115: SET INTERNAL_TRX_ID = ln_internal_trx_id,
5116: INTERNAL_TRX_LINE_ID = ln_internal_trx_line_id
5117: WHERE INTERFACE_LINE_ID = rec_interface_line.INTERFACE_LINE_ID;
5118: */--Update both column move to initial_process

Line 5172: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE

5168: /*
5169: || Initial process
5170: */
5171: PROCEDURE initial_process(
5172: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE
5173: )
5174: IS
5175: --Cursor define here
5176: CURSOR get_line_info_cur

Line 5184: FROM JAI_INTERFACE_LINES_ALL

5180: IMPORT_MODULE,
5181: PARTY_ID,
5182: PARTY_SITE_ID,
5183: ORG_ID
5184: FROM JAI_INTERFACE_LINES_ALL
5185: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
5186:
5187: --Variable define here
5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;

Line 5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;

5184: FROM JAI_INTERFACE_LINES_ALL
5185: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
5186:
5187: --Variable define here
5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;
5189: ln_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
5190: lv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE;
5191: ln_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
5192: ln_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;

Line 5189: ln_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;

5185: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
5186:
5187: --Variable define here
5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;
5189: ln_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
5190: lv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE;
5191: ln_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
5192: ln_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
5193: lv_return_mess VARCHAR2(1000);

Line 5190: lv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE;

5186:
5187: --Variable define here
5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;
5189: ln_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
5190: lv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE;
5191: ln_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
5192: ln_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
5193: lv_return_mess VARCHAR2(1000);
5194: lv_error_mess varchar2(1000);

Line 5191: ln_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;

5187: --Variable define here
5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;
5189: ln_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
5190: lv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE;
5191: ln_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
5192: ln_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
5193: lv_return_mess VARCHAR2(1000);
5194: lv_error_mess varchar2(1000);
5195: ln_err_flag_init NUMBER;

Line 5192: ln_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;

5188: lv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE;
5189: ln_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE;
5190: lv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE;
5191: ln_trx_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE;
5192: ln_trx_line_id JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_LINE_ID%TYPE;
5193: lv_return_mess VARCHAR2(1000);
5194: lv_error_mess varchar2(1000);
5195: ln_err_flag_init NUMBER;
5196: ln_phase NUMBER;

Line 5329: update jai_interface_lines_all

5325: IF(ln_err_flag_init = 0)THEN
5326:
5327: ln_phase := 10.8;
5328:
5329: update jai_interface_lines_all
5330: set internal_trx_id = ln_trx_id,
5331: internal_trx_line_id = ln_trx_line_id
5332: where interface_line_id = pn_interface_line_id;
5333:

Line 5509: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

5505: /*
5506: ||Populate temporary table JAI_AR_TRX_INS_LINES_T
5507: */
5508: PROCEDURE populate_temp_table(
5509: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
5510: pn_customer_trx_id IN ra_customer_trx_all.customer_trx_id%type,
5511: pn_customer_trx_line_id IN jai_ar_trx_lines.customer_trx_line_id%type,
5512: pv_return_code OUT NOCOPY VARCHAR2,
5513: pv_return_message OUT NOCOPY VARCHAR2)

Line 6632: jai_interface_lines_all intfs

6628:
6629: SELECT COUNT(lines.customer_trx_id)
6630: INTO ln_external_flag
6631: FROM jai_ar_trx_lines lines,
6632: jai_interface_lines_all intfs
6633: WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
6634: AND lines.customer_trx_id = pn_customer_trx_id
6635: AND lines.interface_flag = 'Y'
6636: AND intfs.taxable_event = 'EXTERNAL';

Line 7065: from JAI_INTERFACE_LINES_ALL

7061: -----------------------------------------------------------
7062: Cursor get_org_info_cur
7063: IS
7064: SELECT organization_id,location_id
7065: from JAI_INTERFACE_LINES_ALL
7066: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
7067:
7068: ln_organization_id number;
7069: ln_location_id number;

Line 8015: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,

8011: /*
8012: || process ar accounting,update reposotory,account inclusive tax.
8013: */
8014: PROCEDURE process_ar_updating_lines(
8015: pn_interface_line_id IN JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE,
8016: ov_return_code OUT NOCOPY VARCHAR2,
8017: ov_return_message OUT NOCOPY VARCHAR2
8018: )
8019: IS

Line 8025: ln_interface_line_id JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE;

8021: --VARIABLE DEFINITION AS BELOW
8022: lv_return_code VARCHAR2(100);
8023: lv_return_mess VARCHAR2(2000);
8024: ln_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE;
8025: ln_interface_line_id JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE;
8026: ln_customer_trx_line_id JAI_AR_TRX_LINES.CUSTOMER_TRX_LINE_ID%TYPE;
8027: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;
8028:
8029: --CURSOR DEFINITION AS BELOW

Line 8027: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;

8023: lv_return_mess VARCHAR2(2000);
8024: ln_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE;
8025: ln_interface_line_id JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE;
8026: ln_customer_trx_line_id JAI_AR_TRX_LINES.CUSTOMER_TRX_LINE_ID%TYPE;
8027: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;
8028:
8029: --CURSOR DEFINITION AS BELOW
8030: CURSOR get_interface_line_cur
8031: IS

Line 8039: FROM JAI_INTERFACE_LINES_ALL

8035: TAXABLE_EVENT,
8036: VAT_INVOICE_NO,
8037: EXCISE_INVOICE_NO,
8038: TAX_CATEGORY_ID
8039: FROM JAI_INTERFACE_LINES_ALL
8040: WHERE INTERFACE_LINE_ID = pn_interface_line_id;
8041:
8042: CURSOR get_vat_tax_exists_cur IS
8043: SELECT 'Y'

Line 8048: jai_interface_lines_all jila

8044: FROM dual
8045: WHERE EXISTS (SELECT 1
8046: FROM jai_cmn_taxes_all jcta,
8047: jai_interface_tax_lines_all jitla,
8048: jai_interface_lines_all jila
8049: WHERE jitla.tax_id = jcta.tax_id
8050: AND jila.interface_line_id = jitla.interface_line_id
8051: --AND jila.interface_line_id = pn_interface_line_id --Commented by zhiwei for Bug#12564566 on 20110523
8052: --Added by zhiwei for bug#12564566 begin

Line 8057: from jai_interface_lines_all

8053: ----------------------------------------------
8054: AND jila.interface_line_id in
8055: (
8056: select interface_line_id
8057: from jai_interface_lines_all
8058: where internal_trx_id = ln_customer_trx_id
8059: )
8060: ----------------------------------------------
8061: --Added by zhiwei for bug#12564566 end

Line 8076: jai_interface_lines_all jila

8072: FROM dual
8073: WHERE EXISTS (SELECT 1
8074: FROM jai_cmn_taxes_all jcta,
8075: jai_interface_tax_lines_all jitla,
8076: jai_interface_lines_all jila
8077: WHERE jitla.tax_id = jcta.tax_id
8078: AND jila.interface_line_id = jitla.interface_line_id
8079: --AND jila.interface_line_id = pn_interface_line_id--Commented by zhiwei for Bug#12564566 on 20110523
8080: --Added by zhiwei for bug#12564566 begin

Line 8085: from jai_interface_lines_all

8081: ----------------------------------------------
8082: AND jila.interface_line_id in
8083: (
8084: select interface_line_id
8085: from jai_interface_lines_all
8086: where internal_trx_id = ln_customer_trx_id
8087: )
8088: ----------------------------------------------
8089: --Added by zhiwei for bug#12564566 end

Line 8098: FROM JAI_INTERFACE_LINES_ALL

8094: CURSOR get_trx_vat_no_cur
8095: IS
8096: SELECT
8097: distinct(VAT_INVOICE_NO)
8098: FROM JAI_INTERFACE_LINES_ALL
8099: WHERE internal_trx_id = ln_customer_trx_id
8100: and nvl(VAT_INVOICE_NO,'###')<> '###';
8101:
8102: CURSOR get_trx_excise_no_cur

Line 8106: FROM JAI_INTERFACE_LINES_ALL

8102: CURSOR get_trx_excise_no_cur
8103: IS
8104: SELECT
8105: distinct(EXCISE_INVOICE_NO)
8106: FROM JAI_INTERFACE_LINES_ALL
8107: WHERE internal_trx_id = ln_customer_trx_id
8108: and nvl(EXCISE_INVOICE_NO,'###')<> '###';
8109: --------------------------------------------
8110: --Added by zhiwei for bug#12564566 end

Line 8115: ln_vat_invoice_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;

8111:
8112:
8113: --VARIABLE DEFINITION
8114: ln_sum_tax_amount JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE;
8115: ln_vat_invoice_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;
8116: ln_excise_invoice_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
8117: rec_interface_line get_interface_line_cur%ROWTYPE;
8118:
8119: ln_tax_category_id JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE;

Line 8116: ln_excise_invoice_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;

8112:
8113: --VARIABLE DEFINITION
8114: ln_sum_tax_amount JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE;
8115: ln_vat_invoice_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;
8116: ln_excise_invoice_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
8117: rec_interface_line get_interface_line_cur%ROWTYPE;
8118:
8119: ln_tax_category_id JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE;
8120:

Line 8119: ln_tax_category_id JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE;

8115: ln_vat_invoice_no JAI_INTERFACE_LINES_ALL.VAT_INVOICE_NO%TYPE;
8116: ln_excise_invoice_no JAI_INTERFACE_LINES_ALL.EXCISE_INVOICE_NO%TYPE;
8117: rec_interface_line get_interface_line_cur%ROWTYPE;
8118:
8119: ln_tax_category_id JAI_INTERFACE_LINES_ALL.TAX_CATEGORY_ID%TYPE;
8120:
8121: ln_vat_assessable_value NUMBER;
8122: ln_excise_assessable_value NUMBER;
8123:

Line 8137: jai_interface_lines_all jila

8133: FROM dual
8134: WHERE EXISTS (SELECT 1
8135: FROM jai_cmn_taxes_all jcta,
8136: jai_interface_tax_lines_all jitla,
8137: jai_interface_lines_all jila
8138: WHERE jitla.tax_id = jcta.tax_id
8139: AND jila.interface_line_id = jitla.interface_line_id
8140: AND jila.interface_line_id in
8141: (

Line 8143: from jai_interface_lines_all

8139: AND jila.interface_line_id = jitla.interface_line_id
8140: AND jila.interface_line_id in
8141: (
8142: select interface_line_id
8143: from jai_interface_lines_all
8144: where internal_trx_id = ln_customer_trx_id
8145: )
8146: AND upper(jcta.tax_type) IN
8147: (SELECT upper(jrr.attribute_code)

Line 8157: FROM JAI_INTERFACE_LINES_ALL

8153: CURSOR get_trx_st_no_cur
8154: IS
8155: SELECT
8156: DISTINCT(st_inv_number)
8157: FROM JAI_INTERFACE_LINES_ALL
8158: WHERE internal_trx_id = ln_customer_trx_id
8159: AND nvl(st_inv_number,'###')<> '###';
8160:
8161: lv_st_invoice_num jai_interface_lines_all.ST_INV_NUMBER%TYPE;

Line 8161: lv_st_invoice_num jai_interface_lines_all.ST_INV_NUMBER%TYPE;

8157: FROM JAI_INTERFACE_LINES_ALL
8158: WHERE internal_trx_id = ln_customer_trx_id
8159: AND nvl(st_inv_number,'###')<> '###';
8160:
8161: lv_st_invoice_num jai_interface_lines_all.ST_INV_NUMBER%TYPE;
8162: lv_st_tax_exists VARCHAR2(1);
8163: --Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952
8164:
8165: --PROGRAM BLOCK BEGIN

Line 8393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,

8389: /*
8390: || process ar accounting,update reposotory,account inclusive tax.
8391: */
8392: PROCEDURE process_ar_accounting_lines(
8393: pn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE,
8394: ov_return_code OUT NOCOPY VARCHAR2,
8395: ov_return_message OUT NOCOPY VARCHAR2
8396: )
8397: IS

Line 8400: FROM jai_interface_lines_all

8396: )
8397: IS
8398: CURSOR get_taxable_event_cur IS
8399: SELECT DISTINCT(taxable_event)
8400: FROM jai_interface_lines_all
8401: WHERE internal_trx_id = pn_trx_id;
8402:
8403: --VARIABLE DEFINITION AS BELOW
8404: lv_return_code VARCHAR2(100);

Line 8407: ln_interface_line_id JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE;

8403: --VARIABLE DEFINITION AS BELOW
8404: lv_return_code VARCHAR2(100);
8405: lv_return_message VARCHAR2(2000);
8406: ln_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE;
8407: ln_interface_line_id JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE;
8408: ln_customer_trx_line_id JAI_AR_TRX_LINES.CUSTOMER_TRX_LINE_ID%TYPE;
8409: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;
8410:
8411:

Line 8409: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;

8405: lv_return_message VARCHAR2(2000);
8406: ln_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE;
8407: ln_interface_line_id JAI_INTERFACE_LINES_ALL.INTERFACE_LINE_ID%TYPE;
8408: ln_customer_trx_line_id JAI_AR_TRX_LINES.CUSTOMER_TRX_LINE_ID%TYPE;
8409: lv_taxable_event JAI_INTERFACE_LINES_ALL.TAXABLE_EVENT%TYPE;
8410:
8411:
8412: ln_phase NUMBER;
8413: le_exception EXCEPTION;

Line 10346: FROM jai_interface_lines_all

10342:
10343: CURSOR line_status_cur IS
10344: SELECT import_status,
10345: import_process
10346: FROM jai_interface_lines_all
10347: WHERE interface_line_id = pn_interface_line_id;
10348:
10349: BEGIN
10350:

Line 10368: jai_interface_lines_all lines,

10364: SELECT
10365: tax.code_combination_id
10366: FROM
10367: --jai_interface_dis_lines_all dist,
10368: jai_interface_lines_all lines,
10369: jai_interface_tax_lines_all tax
10370: WHERE tax.tax_line_no = pn_tax_line_no
10371: AND tax.interface_line_id = lines.interface_line_id
10372: AND lines.internal_trx_id = pn_invoice_id

Line 10485: lv_taxable_event jai_interface_lines_all.taxable_event%type;

10481: ln_threshold_tax_cat_id number;
10482: ln_tax_amount NUMBER;
10483: lv_process_flag varchar2(100);
10484: lv_process_message varchar2(1000);
10485: lv_taxable_event jai_interface_lines_all.taxable_event%type;
10486: ln_internal_trx_line_id jai_interface_lines_all.internal_trx_line_id%type;
10487: ln_internal_trx_id jai_interface_lines_all.internal_trx_id%type;
10488: l_tcs_sur_line_num number;
10489: l_tot_tax_lines number;

Line 10486: ln_internal_trx_line_id jai_interface_lines_all.internal_trx_line_id%type;

10482: ln_tax_amount NUMBER;
10483: lv_process_flag varchar2(100);
10484: lv_process_message varchar2(1000);
10485: lv_taxable_event jai_interface_lines_all.taxable_event%type;
10486: ln_internal_trx_line_id jai_interface_lines_all.internal_trx_line_id%type;
10487: ln_internal_trx_id jai_interface_lines_all.internal_trx_id%type;
10488: l_tcs_sur_line_num number;
10489: l_tot_tax_lines number;
10490:

Line 10487: ln_internal_trx_id jai_interface_lines_all.internal_trx_id%type;

10483: lv_process_flag varchar2(100);
10484: lv_process_message varchar2(1000);
10485: lv_taxable_event jai_interface_lines_all.taxable_event%type;
10486: ln_internal_trx_line_id jai_interface_lines_all.internal_trx_line_id%type;
10487: ln_internal_trx_id jai_interface_lines_all.internal_trx_id%type;
10488: l_tcs_sur_line_num number;
10489: l_tot_tax_lines number;
10490:
10491: CURSOR BOOKS_TRX_CUR(cn_customer_trx_id number)

Line 10529: FROM jai_interface_lines_all

10525: l_max_tax_line_no number;
10526:
10527: CURSOR get_taxable_event_cur IS
10528: SELECT taxable_event
10529: FROM jai_interface_lines_all
10530: WHERE interface_line_id = pn_interface_line_id;
10531:
10532: ln_p1 NUMBER;
10533: ln_p2 NUMBER;

Line 10704: , jai_interface_lines_all c

10700:
10701: $$EXTRA_SELECT_COLUMN_LIST$$
10702: FROM $$TAX_SOURCE_TABLE$$ a
10703: , JAI_CMN_TAXES_ALL b
10704: , jai_interface_lines_all c
10705: WHERE $$SOURCE_TABLE_FILTER$$
10706: AND a.tax_id = b.tax_id
10707: $$ADDITIONAL_WHERE_CLAUSE$$
10708: ORDER BY $$ADDITIONAL_ORDER_BY$$';

Line 10762: -- Tax_category_id is populated in jai_interface_lines_all during VALIDATION phase.

10758: , 'a.interface_line_id = ' || pn_interface_line_id
10759: );
10760:
10761: ELSE -- No related tax lines in JAI_INTERFACE_TAX_LINES_ALL, tax category will be used.
10762: -- Tax_category_id is populated in jai_interface_lines_all during VALIDATION phase.
10763: -- Will fetch taxes from JAI_CMN_TAX_CTG_LINES
10764:
10765: SELECT tax_category_id
10766: INTO ln_tax_category_id

Line 10767: FROM jai_interface_lines_all

10763: -- Will fetch taxes from JAI_CMN_TAX_CTG_LINES
10764:
10765: SELECT tax_category_id
10766: INTO ln_tax_category_id
10767: FROM jai_interface_lines_all
10768: WHERE interface_line_id = pn_interface_line_id;
10769:
10770: IF ln_tax_category_id IS NOT NULL THEN
10771:

Line 11008: ln_header_id jai_interface_lines_all.internal_trx_id%TYPE;

11004: set_of_books_id
11005: FROM ra_customer_trx_all A
11006: WHERE customer_trx_id = pn_customer_trx_id;
11007:
11008: ln_header_id jai_interface_lines_all.internal_trx_id%TYPE;
11009: ln_line_num jai_interface_lines_all.internal_trx_line_id%TYPE;
11010:
11011: ln_vendor_id NUMBER;
11012: ln_vendor_site_id NUMBER;

Line 11009: ln_line_num jai_interface_lines_all.internal_trx_line_id%TYPE;

11005: FROM ra_customer_trx_all A
11006: WHERE customer_trx_id = pn_customer_trx_id;
11007:
11008: ln_header_id jai_interface_lines_all.internal_trx_id%TYPE;
11009: ln_line_num jai_interface_lines_all.internal_trx_line_id%TYPE;
11010:
11011: ln_vendor_id NUMBER;
11012: ln_vendor_site_id NUMBER;
11013: ln_line_amount NUMBER;

Line 11016: lv_taxable_basis jai_interface_lines_all.taxable_basis%TYPE;

11012: ln_vendor_site_id NUMBER;
11013: ln_line_amount NUMBER;
11014: ln_assessable_value NUMBER := 0;
11015: ln_vat_assessable_value NUMBER := 0;
11016: lv_taxable_basis jai_interface_lines_all.taxable_basis%TYPE;
11017:
11018: ln_item_id NUMBER;
11019: ln_quantity NUMBER;
11020: lv_unit_code VARCHAR2(10);

Line 11043: FROM jai_interface_lines_all

11039: INTO ln_header_id
11040: , ln_line_num
11041: , lv_taxable_basis
11042: , lv_module
11043: FROM jai_interface_lines_all
11044: WHERE interface_line_id = pn_interface_line_id;
11045:
11046: IF lv_module = 'AP' THEN
11047: SELECT vendor_id, vendor_site_id

Line 11193: FROM jai_interface_lines_all

11189: location_id,
11190: taxable_event,
11191: service_type_code,
11192: tax_category_id
11193: FROM jai_interface_lines_all
11194: WHERE interface_line_id = pn_interface_line_id;
11195:
11196: ln_interface_line_id NUMBER;
11197: ln_invoice_id NUMBER;

Line 11288: JAI_INTERFACE_LINES_ALL

11284: TRANSACTION_NUM,
11285: TRANSACTION_LINE_NUM,
11286: INTERFACE_LINE_ID
11287: FROM
11288: JAI_INTERFACE_LINES_ALL
11289: WHERE REQUEST_ID = GN_REQUEST_ID;*/
11290: ----------------------------------------------------------
11291:
11292: --Commented by zhiwei for Bug#12561396 and Bug#12589644 on 20110607 end

Line 11297: cn_party_id JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

11293:
11294: --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607 begin
11295: ----------------------------------------------------------------
11296: CURSOR get_interface_lines_rec(
11297: cn_party_id JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11298: cn_party_site_id JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11299: cv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11300: cv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11301: cn_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE

Line 11298: cn_party_site_id JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,

11294: --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607 begin
11295: ----------------------------------------------------------------
11296: CURSOR get_interface_lines_rec(
11297: cn_party_id JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11298: cn_party_site_id JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11299: cv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11300: cv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11301: cn_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11302: )

Line 11299: cv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

11295: ----------------------------------------------------------------
11296: CURSOR get_interface_lines_rec(
11297: cn_party_id JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11298: cn_party_site_id JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11299: cv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11300: cv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11301: cn_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11302: )
11303: IS

Line 11300: cv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,

11296: CURSOR get_interface_lines_rec(
11297: cn_party_id JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11298: cn_party_site_id JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11299: cv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11300: cv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11301: cn_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11302: )
11303: IS
11304: SELECT

Line 11301: cn_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE

11297: cn_party_id JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11298: cn_party_site_id JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11299: cv_import_module JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11300: cv_transaction_num JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11301: cn_transaction_line_num JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11302: )
11303: IS
11304: SELECT
11305: PARTY_ID,

Line 11313: JAI_INTERFACE_LINES_ALL

11309: TRANSACTION_LINE_NUM,
11310: INTERFACE_LINE_ID,
11311: ROWID
11312: FROM
11313: JAI_INTERFACE_LINES_ALL
11314: WHERE REQUEST_ID = GN_REQUEST_ID
11315: AND PARTY_ID = cn_party_id
11316: AND PARTY_SITE_ID = cn_party_site_id
11317: AND IMPORT_MODULE = cv_import_module

Line 11331: JAI_INTERFACE_LINES_ALL

11327: IMPORT_MODULE,
11328: TRANSACTION_NUM,
11329: TRANSACTION_LINE_NUM
11330: FROM
11331: JAI_INTERFACE_LINES_ALL
11332: WHERE REQUEST_ID = GN_REQUEST_ID
11333: ;
11334:
11335: rec_five_combination get_five_combination_rec%rowtype;

Line 11341: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,

11337: ----------------------------------------------------------------
11338: --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607 end
11339:
11340: CURSOR get_tax_line_no_rec(
11341: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11342: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11343: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11344: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11345: cn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE

Line 11342: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,

11338: --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607 end
11339:
11340: CURSOR get_tax_line_no_rec(
11341: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11342: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11343: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11344: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11345: cn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11346:

Line 11343: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,

11339:
11340: CURSOR get_tax_line_no_rec(
11341: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11342: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11343: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11344: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11345: cn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11346:
11347: )

Line 11344: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,

11340: CURSOR get_tax_line_no_rec(
11341: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11342: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11343: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11344: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11345: cn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11346:
11347: )
11348: IS

Line 11345: cn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE

11341: cn_party_id IN JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE,
11342: cn_party_site_id IN JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE,
11343: cv_import_module IN JAI_INTERFACE_LINES_ALL.IMPORT_MODULE%TYPE,
11344: cv_transaction_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_NUM%TYPE,
11345: cn_transaction_line_num IN JAI_INTERFACE_LINES_ALL.TRANSACTION_LINE_NUM%TYPE
11346:
11347: )
11348: IS
11349: SELECT

Line 11407: ln_interface_line_id := JAI_INTERFACE_LINES_ALL_S.nextval;

11403: ln_interface_line_id := 0;
11404:
11405: IF(NVL(rec_interface_line.interface_line_id,-1) <= 0)THEN
11406:
11407: ln_interface_line_id := JAI_INTERFACE_LINES_ALL_S.nextval;
11408:
11409: --Mark line sequence no
11410: UPDATE jai_interface_lines_all
11411: SET interface_line_id = ln_interface_line_id

Line 11410: UPDATE jai_interface_lines_all

11406:
11407: ln_interface_line_id := JAI_INTERFACE_LINES_ALL_S.nextval;
11408:
11409: --Mark line sequence no
11410: UPDATE jai_interface_lines_all
11411: SET interface_line_id = ln_interface_line_id
11412: WHERE party_id = rec_interface_line.party_id
11413: AND party_site_id = rec_interface_line.PARTY_SITE_ID
11414: AND import_module = rec_interface_line.IMPORT_MODULE

Line 11441: --(interface_line_id is null or interface_line_id is not as same as interface_line_id of jai_interface_lines_all accordingly)

11437: ln_interface_tax_line_id := JAI_INTERFACE_TAX_LINES_ALL_S.nextval;
11438:
11439: --If interface_tax_line_id is null
11440: -- and
11441: --(interface_line_id is null or interface_line_id is not as same as interface_line_id of jai_interface_lines_all accordingly)
11442: --both mark
11443: IF(NVL(rec_tax_line_no.interface_line_id,-1) = -1
11444: OR
11445: NVL(rec_tax_line_no.interface_line_id,-1) <> ln_interface_line_id

Line 11462: --interface_line_id is as same as interface_line_id of jai_interface_lines_all accordingly

11458: AND ROWID = rec_tax_line_no.ROWID; --Added by zhiwei for Bug#12561396 and Bug#12589644 on 20110607
11459: ELSE
11460: --interface_tax_line_id is null
11461: --and
11462: --interface_line_id is as same as interface_line_id of jai_interface_lines_all accordingly
11463: --only mark interface_tax_line_id
11464: UPDATE jai_interface_tax_lines_all
11465: SET interface_tax_line_id = ln_interface_tax_line_id,
11466: tax_line_no = ln_tax_line_no

Line 11550: from jai_interface_lines_all

11546: --Cursor define here
11547: CURSOR get_request_line_id_cur
11548: is
11549: select interface_line_id
11550: from jai_interface_lines_all
11551: where 1=1 --(import_status=GV_IMPORT_FAILURE OR import_status IS NULL)
11552: and request_id = GN_REQUEST_ID;
11553:
11554: CURSOR interface_lines_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

Line 11554: CURSOR interface_lines_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

11550: from jai_interface_lines_all
11551: where 1=1 --(import_status=GV_IMPORT_FAILURE OR import_status IS NULL)
11552: and request_id = GN_REQUEST_ID;
11553:
11554: CURSOR interface_lines_cur(cn_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
11555: IS
11556: SELECT interface_line_id,
11557: import_status,
11558: import_process

Line 11559: FROM jai_interface_lines_all

11555: IS
11556: SELECT interface_line_id,
11557: import_status,
11558: import_process
11559: FROM jai_interface_lines_all
11560: WHERE import_status = GV_IMPORT_SUCCESS
11561: AND import_process = GV_PROCESS_VALIDATION
11562: AND import_module = pv_import_module
11563: AND internal_trx_id = cn_trx_id

Line 11569: FROM JAI_INTERFACE_LINES_ALL

11565:
11566: CURSOR get_trx_id_cur
11567: IS
11568: SELECT DISTINCT internal_trx_id trx_id
11569: FROM JAI_INTERFACE_LINES_ALL
11570: WHERE import_status = GV_IMPORT_SUCCESS
11571: AND import_process = GV_PROCESS_VALIDATION
11572: AND import_module = pv_import_module
11573: AND organization_id = nvl(pn_organization_id, organization_id)

Line 11581: CURSOR get_interface_id_cur(cn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)

11577: AND transaction_num <= nvl(pv_transaction_num_to, transaction_num)
11578: AND REQUEST_ID = GN_REQUEST_ID
11579: ORDER BY 1;
11580:
11581: CURSOR get_interface_id_cur(cn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
11582: IS
11583: SELECT
11584: interface_line_id
11585: FROM JAI_INTERFACE_LINES_ALL

Line 11585: FROM JAI_INTERFACE_LINES_ALL

11581: CURSOR get_interface_id_cur(cn_internal_trx_id IN JAI_INTERFACE_LINES_ALL.INTERNAL_TRX_ID%TYPE)
11582: IS
11583: SELECT
11584: interface_line_id
11585: FROM JAI_INTERFACE_LINES_ALL
11586: WHERE INTERNAL_TRX_ID = cn_internal_trx_id;
11587:
11588: --Variable define here
11589: rec_interface_id get_interface_id_cur%ROWTYPE;

Line 11603: ln_interface_line_id jai_interface_lines_all.interface_line_id%TYPE;

11599: ln_invoice_line_num NUMBER;
11600: lv_import_status VARCHAR2(30);
11601: lv_import_process VARCHAR2(30);
11602: lv_err_mess VARCHAR2(300);
11603: ln_interface_line_id jai_interface_lines_all.interface_line_id%TYPE;
11604: lv_taxable_event jai_interface_lines_all.taxable_event%TYPE;
11605: lv_process_message VARCHAR2(2000);
11606: le_exception EXCEPTION;
11607:

Line 11604: lv_taxable_event jai_interface_lines_all.taxable_event%TYPE;

11600: lv_import_status VARCHAR2(30);
11601: lv_import_process VARCHAR2(30);
11602: lv_err_mess VARCHAR2(300);
11603: ln_interface_line_id jai_interface_lines_all.interface_line_id%TYPE;
11604: lv_taxable_event jai_interface_lines_all.taxable_event%TYPE;
11605: lv_process_message VARCHAR2(2000);
11606: le_exception EXCEPTION;
11607:
11608: lv_return_message VARCHAR2(2000);

Line 11739: FROM jai_interface_lines_all

11735: IF lv_import_status = GV_IMPORT_SUCCESS THEN
11736:
11737: SELECT taxable_event
11738: INTO lv_taxable_event
11739: FROM jai_interface_lines_all
11740: WHERE interface_line_id = ln_interface_line_id;
11741:
11742: SELECT internal_trx_id, internal_trx_line_id
11743: INTO ln_invoice_id, ln_invoice_line_num

Line 11744: FROM jai_interface_lines_all

11740: WHERE interface_line_id = ln_interface_line_id;
11741:
11742: SELECT internal_trx_id, internal_trx_line_id
11743: INTO ln_invoice_id, ln_invoice_line_num
11744: FROM jai_interface_lines_all
11745: WHERE interface_line_id = ln_interface_line_id;
11746:
11747: --ap_invoice_lines_all.reference_key3 values 'OFI TAX IMPORT'
11748: /* The purpose is for distinguish Standard or External Case,

Line 12127: , pn_party JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE

12123: , pv_import_module VARCHAR2
12124: , pn_org_id NUMBER
12125: , pn_organization_id NUMBER
12126: , pn_location_id NUMBER
12127: , pn_party JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE
12128: , pn_party_site JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE
12129: , pv_transaction_num_from VARCHAR2
12130: , pv_transaction_num_to VARCHAR2
12131: , pv_line_status VARCHAR2

Line 12128: , pn_party_site JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE

12124: , pn_org_id NUMBER
12125: , pn_organization_id NUMBER
12126: , pn_location_id NUMBER
12127: , pn_party JAI_INTERFACE_LINES_ALL.PARTY_ID%TYPE
12128: , pn_party_site JAI_INTERFACE_LINES_ALL.PARTY_SITE_ID%TYPE
12129: , pv_transaction_num_from VARCHAR2
12130: , pv_transaction_num_to VARCHAR2
12131: , pv_line_status VARCHAR2
12132: )

Line 12162: from jai_interface_lines_all

12158: where 1=1
12159: and interface_line_id in
12160: (
12161: select interface_line_id
12162: from jai_interface_lines_all
12163: where 1=1
12164: AND import_module = nvl(pv_import_module,import_module)
12165: AND organization_id = nvl(pn_organization_id, organization_id)
12166: AND org_id = nvl(pn_org_id, org_id)

Line 12180: from jai_interface_lines_all

12176: where 1=1
12177: and interface_line_id in
12178: (
12179: select interface_line_id
12180: from jai_interface_lines_all
12181: where 1=1
12182: AND import_module = nvl(pv_import_module,import_module)
12183: AND organization_id = nvl(pn_organization_id, organization_id)
12184: AND org_id = nvl(pn_org_id, org_id)

Line 12193: delete from jai_interface_lines_all

12189: AND party_site_id = nvl(pn_party_site,party_site_id)
12190: and (request_id is null or request_id='')
12191: );
12192:
12193: delete from jai_interface_lines_all
12194: where 1=1
12195: AND import_module = nvl(pv_import_module,import_module)
12196: AND organization_id = nvl(pn_organization_id, organization_id)
12197: AND org_id = nvl(pn_org_id, org_id)

Line 12222: FROM JAI_INTERFACE_LINES_ALL

12218: where 1=1
12219: and interface_line_id in
12220: (
12221: SELECT interface_line_id
12222: FROM JAI_INTERFACE_LINES_ALL
12223: WHERE 1=1
12224: and import_status = GV_IMPORT_FAILURE
12225: AND nvl(error_flag,'N')= 'Y'
12226: AND import_module = nvl(pv_import_module, import_module)

Line 12242: FROM JAI_INTERFACE_LINES_ALL

12238: where 1=1
12239: and interface_line_id in
12240: (
12241: SELECT interface_line_id
12242: FROM JAI_INTERFACE_LINES_ALL
12243: WHERE 1=1
12244: and import_status = GV_IMPORT_FAILURE
12245: AND nvl(error_flag,'N')= 'Y'
12246: AND import_module = nvl(pv_import_module, import_module)

Line 12257: delete from jai_interface_lines_all

12253: AND party_site_id = nvl(pn_party_site,party_site_id)
12254: and (request_id is null or request_id='')
12255: );
12256:
12257: delete from jai_interface_lines_all
12258: WHERE 1=1
12259: and import_status = GV_IMPORT_FAILURE
12260: AND nvl(error_flag,'N')= 'Y'
12261: AND import_module = nvl(pv_import_module, import_module)

Line 12278: FROM JAI_INTERFACE_LINES_ALL

12274: where 1=1
12275: and interface_line_id in
12276: (
12277: SELECT interface_line_id
12278: FROM JAI_INTERFACE_LINES_ALL
12279: WHERE 1=1
12280: AND import_status = GV_IMPORT_SUCCESS
12281: AND import_module = nvl(pv_import_module, import_module)
12282: AND organization_id = nvl(pn_organization_id, organization_id)

Line 12296: FROM JAI_INTERFACE_LINES_ALL

12292: where 1=1
12293: and interface_line_id in
12294: (
12295: SELECT interface_line_id
12296: FROM JAI_INTERFACE_LINES_ALL
12297: WHERE 1=1
12298: AND import_status = GV_IMPORT_SUCCESS
12299: AND import_module = nvl(pv_import_module, import_module)
12300: AND organization_id = nvl(pn_organization_id, organization_id)

Line 12309: delete from jai_interface_lines_all

12305: AND party_id = nvl(pn_party,party_id)
12306: AND party_site_id = nvl(pn_party_site,party_site_id)
12307: );
12308:
12309: delete from jai_interface_lines_all
12310: WHERE 1=1
12311: AND import_status = GV_IMPORT_SUCCESS
12312: AND import_module = nvl(pv_import_module, import_module)
12313: AND organization_id = nvl(pn_organization_id, organization_id)