[Home] [Help]
PACKAGE BODY: APPS.JA_CN_SR_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_SR_EXPORT_PKG AS
2 --$Header: JACNSREB.pls 120.2 2010/05/20 02:48:48 weihuang noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================|
8 --| FILENAME |
9 --| JACNSREB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package contains the following PL/SQL tables/procedures |
14 --| to export suppliers which have transaction with current legal |
15 --| entity in given accounting year. |
16 --| |
17 --| PROCEDURE LIST |
18 --| Add_Supplier |
19 --| |
20 --| HISTORY |
21 --| 01-Mar-2010 Wei Huang Created |
22 --| 19-May-2010 Wei Huang Updated procedure Add_Supplier |
23 --| |
24 --+======================================================================*/
25 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_SR_EXPORT_PKG';
26
27 --==========================================================================
28 -- PROCEDURE NAME:
29 --
30 -- Add_Supplier Public
31 --
32 -- DESCRIPTION:
33 --
34 -- This procedure is to export suppliers which have transaction
35 -- with current legal entity in given accounting year.
36 --
37 -- PARAMETERS:
38 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
39 -- pv_accounting_year Accounting Year
40 --
41 -- Out:
42 --
43 -- DESIGN REFERENCES:
44 --
45 --
46 -- CHANGE HISTORY:
47 -- 01-Mar-2010 Wei Huang Created
48 -- 19-May-2010 Wei Huang Updated the logic of handling NO_DATA Exception
49 --
50 --===========================================================================
51 PROCEDURE Add_Supplier
52 (pn_legal_entity_id IN NUMBER
53 ,pv_accounting_year IN VARCHAR2
54 )
55 IS
56 lv_procedure_name VARCHAR2(40) := 'Add_Supplier';
57 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
58 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
59 --ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
60 NO_DATA EXCEPTION;
61 ln_row_count NUMBER := 0;
62
63 CURSOR supplier_cur
64 IS
65 SELECT DISTINCT
66 DECODE(ai.INVOICE_TYPE_LOOKUP_CODE, 'PAYMENT REQUEST', NULL,
67 PV.SEGMENT1) AS Supplier_Number
68 ,VENDOR_NAME AS Supplier_Name, VENDOR_NAME AS Supplier_Short_Name
69 FROM PO_VENDORS pv, AP_INVOICES_ALL ai
70 WHERE pv.VENDOR_ID = ai.VENDOR_ID
71 AND pv.EMPLOYEE_ID IS NULL --'Standard Supplier' only
72 AND ai.LEGAL_ENTITY_ID = pn_legal_entity_id --LEGAL_ENTITY_ID
73 AND to_char(ai.GL_DATE,'YYYY') = pv_accounting_year --Accounting Year
74 AND AP_INVOICES_PKG.GET_APPROVAL_STATUS( ai.INVOICE_ID,
75 ai.INVOICE_AMOUNT, ai.PAYMENT_STATUS_FLAG,ai.INVOICE_TYPE_LOOKUP_CODE) IN
76 ('APPROVED','AVAILABLE','CANCELLED','FULL','PERMANENT','UNPAID','SELECTED FOR PAYMENT')
77 ORDER BY Supplier_Number;
78
79 BEGIN
80 --logging for debug
81 IF (ln_proc_level >= ln_dbg_level)
82 THEN
83 FND_LOG.STRING(ln_proc_level,
84 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
85 '.begin',
86 'Enter procedure');
87 -- logging the parameters
88 FND_LOG.STRING(ln_proc_level,
89 lv_procedure_name ||
90 '.parameters',
91 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
92 'pv_accounting_year=' || pv_accounting_year);
93 END IF; --ln_proc_level >= ln_dbg_level
94 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
95 '.parameters:' ||
96 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
97 'pv_accounting_year=' || pv_accounting_year);
98
99 --Open and loop the Cursor to add the data to XML file
100 FOR v_row IN supplier_cur
101 LOOP
102 ln_row_count := ln_row_count + 1;
103 Ja_Cn_Utility.Add_Sub_Root_Node('SUPPLIER_RECORD',
104 Ja_Cn_Utility.GV_TAG_TYPE_START);
105 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_NUMBER',
106 v_row.supplier_number);
107 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_NAME',
108 v_row.supplier_name);
109 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_SHORT_NAME',
110 v_row.supplier_short_name);
111 Ja_Cn_Utility.Add_Sub_Root_Node('SUPPLIER_RECORD',
112 Ja_Cn_Utility.GV_TAG_TYPE_END);
113 END LOOP;
114
115 -- To judge if the Cursor fetchs data. If the row count is 0 and the
116 -- minOccurs=0 specified in xml schema, then need to handle it and raise exception
117 IF (ln_row_count = 0)
118 THEN
119 Ja_Cn_Utility.Print_No_Data_Found_For_Log('SUPPLIER_RECORD');
120
121 Ja_Cn_Utility.Add_Sub_Root_Node('SUPPLIER_RECORD',
122 Ja_Cn_Utility.GV_TAG_TYPE_START);
123 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_NUMBER',
124 NULL);
125 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_NAME',
126 NULL);
127 Ja_Cn_Utility.Add_Child_Node('SUPPLIER_SHORT_NAME',
128 NULL);
129 Ja_Cn_Utility.Add_Sub_Root_Node('SUPPLIER_RECORD',
130 Ja_Cn_Utility.GV_TAG_TYPE_END);
131 END IF;
132
133 --logging for debug
134 IF (ln_proc_level >= ln_dbg_level)
135 THEN
136 FND_LOG.STRING(ln_proc_level,
137 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
138 'Exit procedure');
139 END IF; -- (ln_proc_level>=ln_dbg_level)
140
141 --To handle the No Data Found Exception
142 EXCEPTION
143 WHEN OTHERS THEN
144 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
145 THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
147 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
148 '.Other_Exception ',
149 SQLCODE || SQLERRM);
150 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
151 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
152 RAISE;
153
154 END Add_Supplier;
155
156 END JA_CN_SR_EXPORT_PKG;