1 PACKAGE jai_cmn_rg_23d_trxs_pkg AS
2 /* $Header: jai_cmn_rg_23d.pls 120.2 2006/11/23 09:30:30 sacsethi ship $ */
3
4 CURSOR c_get_last_record(p_register_id IN NUMBER) IS
5 SELECT slno, opening_balance_qty, closing_balance_qty, fin_year
6 FROM JAI_CMN_RG_23D_TRXS
7 WHERE register_id = p_register_id;
8
9 PROCEDURE insert_row(
10 P_REGISTER_ID OUT NOCOPY JAI_CMN_RG_23D_TRXS.register_id%TYPE,
11 P_ORGANIZATION_ID IN JAI_CMN_RG_23D_TRXS.organization_id%TYPE,
12 P_LOCATION_ID IN JAI_CMN_RG_23D_TRXS.location_id%TYPE,
13 P_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.transaction_type%TYPE,
14 P_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.receipt_ref%TYPE,
15 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23D_TRXS.quantity_received%TYPE,
16 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23D_TRXS.inventory_item_id%TYPE,
17 P_SUBINVENTORY IN JAI_CMN_RG_23D_TRXS.subinventory%TYPE,
18 P_REFERENCE_LINE_ID IN JAI_CMN_RG_23D_TRXS.reference_line_id%TYPE,
19 P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23D_TRXS.transaction_uom_code%TYPE,
20 P_CUSTOMER_ID IN JAI_CMN_RG_23D_TRXS.customer_id%TYPE,
21 P_BILL_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.bill_to_site_id%TYPE,
22 P_SHIP_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.ship_to_site_id%TYPE,
23 P_QUANTITY_ISSUED IN JAI_CMN_RG_23D_TRXS.quantity_issued%TYPE,
24 P_REGISTER_CODE IN JAI_CMN_RG_23D_TRXS.register_code%TYPE,
25 P_RELEASED_DATE IN JAI_CMN_RG_23D_TRXS.released_date%TYPE,
26 P_COMM_INVOICE_NO IN JAI_CMN_RG_23D_TRXS.comm_invoice_no%TYPE,
27 P_COMM_INVOICE_DATE IN JAI_CMN_RG_23D_TRXS.comm_invoice_date%TYPE,
28 P_RECEIPT_BOE_NUM IN JAI_CMN_RG_23D_TRXS.receipt_boe_num%TYPE,
29 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.oth_receipt_id_ref%TYPE,
30 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.oth_receipt_date%TYPE,
31 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23D_TRXS.oth_receipt_quantity%TYPE,
32 P_REMARKS IN JAI_CMN_RG_23D_TRXS.remarks%TYPE,
33 P_QTY_TO_ADJUST IN JAI_CMN_RG_23D_TRXS.qty_to_adjust%TYPE,
34 P_RATE_PER_UNIT IN JAI_CMN_RG_23D_TRXS.rate_per_unit%TYPE,
35 P_EXCISE_DUTY_RATE IN JAI_CMN_RG_23D_TRXS.excise_duty_rate%TYPE,
36 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23D_TRXS.charge_account_id%TYPE,
37 P_DUTY_AMOUNT IN JAI_CMN_RG_23D_TRXS.duty_amount%TYPE,
38 P_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.receipt_date%TYPE,
39 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23D_TRXS.goods_issue_id%TYPE,
40 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23D_TRXS.goods_issue_date%TYPE,
41 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23D_TRXS.goods_issue_quantity%TYPE,
42 P_TRANSACTION_DATE IN JAI_CMN_RG_23D_TRXS.transaction_date%TYPE,
43 P_BASIC_ED IN JAI_CMN_RG_23D_TRXS.basic_ed%TYPE,
44 P_ADDITIONAL_ED IN JAI_CMN_RG_23D_TRXS.additional_ed%TYPE,
45 P_ADDITIONAL_CVD IN JAI_CMN_RG_23D_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
46 P_OTHER_ED IN JAI_CMN_RG_23D_TRXS.other_ed%TYPE,
47 P_CVD IN JAI_CMN_RG_23D_TRXS.cvd%TYPE,
48 P_VENDOR_ID IN JAI_CMN_RG_23D_TRXS.vendor_id%TYPE,
49 P_VENDOR_SITE_ID IN JAI_CMN_RG_23D_TRXS.vendor_site_id%TYPE,
50 P_RECEIPT_NUM IN JAI_CMN_RG_23D_TRXS.receipt_num%TYPE,
51 P_ATTRIBUTE1 IN JAI_CMN_RG_23D_TRXS.attribute1%TYPE,
52 P_ATTRIBUTE2 IN JAI_CMN_RG_23D_TRXS.attribute2%TYPE,
53 P_ATTRIBUTE3 IN JAI_CMN_RG_23D_TRXS.attribute3%TYPE,
54 P_ATTRIBUTE4 IN JAI_CMN_RG_23D_TRXS.attribute4%TYPE,
55 P_ATTRIBUTE5 IN JAI_CMN_RG_23D_TRXS.attribute5%TYPE,
56 P_CONSIGNEE IN JAI_CMN_RG_23D_TRXS.consignee%TYPE,
57 P_MANUFACTURER_NAME IN JAI_CMN_RG_23D_TRXS.manufacturer_name%TYPE,
58 P_MANUFACTURER_ADDRESS IN JAI_CMN_RG_23D_TRXS.manufacturer_address%TYPE,
59 P_MANUFACTURER_RATE_AMT_PER_UN IN JAI_CMN_RG_23D_TRXS.manufacturer_rate_amt_per_unit%TYPE,
60 P_QTY_RECEIVED_FROM_MANUFACTUR IN JAI_CMN_RG_23D_TRXS.qty_received_from_manufacturer%TYPE,
61 P_TOT_AMT_PAID_TO_MANUFACTURER IN JAI_CMN_RG_23D_TRXS.tot_amt_paid_to_manufacturer%TYPE,
62 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
63 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23D_TRXS.other_tax_credit%TYPE,
64 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23D_TRXS.other_tax_debit%TYPE,
65 P_TRANSACTION_SOURCE IN VARCHAR2,
66 P_CALLED_FROM IN VARCHAR2,
67 P_SIMULATE_FLAG IN VARCHAR2,
68 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
69 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
70 );
71
72 PROCEDURE update_row(
73 P_REGISTER_ID IN JAI_CMN_RG_23D_TRXS.register_id%TYPE DEFAULT NULL,
74 P_ORGANIZATION_ID IN JAI_CMN_RG_23D_TRXS.organization_id%TYPE DEFAULT NULL,
75 P_LOCATION_ID IN JAI_CMN_RG_23D_TRXS.location_id%TYPE DEFAULT NULL,
76 P_SLNO IN JAI_CMN_RG_23D_TRXS.slno%TYPE DEFAULT NULL,
77 P_FIN_YEAR IN JAI_CMN_RG_23D_TRXS.fin_year%TYPE DEFAULT NULL,
78 P_TRANSACTION_TYPE IN JAI_CMN_RG_23D_TRXS.transaction_type%TYPE DEFAULT NULL,
79 P_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.receipt_ref%TYPE DEFAULT NULL,
80 P_QUANTITY_RECEIVED IN JAI_CMN_RG_23D_TRXS.quantity_received%TYPE DEFAULT NULL,
81 P_INVENTORY_ITEM_ID IN JAI_CMN_RG_23D_TRXS.inventory_item_id%TYPE DEFAULT NULL,
82 P_SUBINVENTORY IN JAI_CMN_RG_23D_TRXS.subinventory%TYPE DEFAULT NULL,
83 P_REFERENCE_LINE_ID IN JAI_CMN_RG_23D_TRXS.reference_line_id%TYPE DEFAULT NULL,
84 P_PRIMARY_UOM_CODE IN JAI_CMN_RG_23D_TRXS.primary_uom_code%TYPE DEFAULT NULL,
85 P_TRANSACTION_UOM_CODE IN JAI_CMN_RG_23D_TRXS.transaction_uom_code%TYPE DEFAULT NULL,
86 P_CUSTOMER_ID IN JAI_CMN_RG_23D_TRXS.customer_id%TYPE DEFAULT NULL,
87 P_BILL_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.bill_to_site_id%TYPE DEFAULT NULL,
88 P_SHIP_TO_SITE_ID IN JAI_CMN_RG_23D_TRXS.ship_to_site_id%TYPE DEFAULT NULL,
89 P_QUANTITY_ISSUED IN JAI_CMN_RG_23D_TRXS.quantity_issued%TYPE DEFAULT NULL,
90 P_REGISTER_CODE IN JAI_CMN_RG_23D_TRXS.register_code%TYPE DEFAULT NULL,
91 P_RELEASED_DATE IN JAI_CMN_RG_23D_TRXS.released_date%TYPE DEFAULT NULL,
92 P_COMM_INVOICE_NO IN JAI_CMN_RG_23D_TRXS.comm_invoice_no%TYPE DEFAULT NULL,
93 P_COMM_INVOICE_DATE IN JAI_CMN_RG_23D_TRXS.comm_invoice_date%TYPE DEFAULT NULL,
94 P_RECEIPT_BOE_NUM IN JAI_CMN_RG_23D_TRXS.receipt_boe_num%TYPE DEFAULT NULL,
95 P_OTH_RECEIPT_ID IN JAI_CMN_RG_23D_TRXS.oth_receipt_id_ref%TYPE DEFAULT NULL,
96 P_OTH_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.oth_receipt_date%TYPE DEFAULT NULL,
97 P_OTH_RECEIPT_QUANTITY IN JAI_CMN_RG_23D_TRXS.oth_receipt_quantity%TYPE DEFAULT NULL,
98 P_REMARKS IN JAI_CMN_RG_23D_TRXS.remarks%TYPE DEFAULT NULL,
99 P_QTY_TO_ADJUST IN JAI_CMN_RG_23D_TRXS.qty_to_adjust%TYPE DEFAULT NULL,
100 P_RATE_PER_UNIT IN JAI_CMN_RG_23D_TRXS.rate_per_unit%TYPE DEFAULT NULL,
101 P_EXCISE_DUTY_RATE IN JAI_CMN_RG_23D_TRXS.excise_duty_rate%TYPE DEFAULT NULL,
102 P_CHARGE_ACCOUNT_ID IN JAI_CMN_RG_23D_TRXS.charge_account_id%TYPE DEFAULT NULL,
103 P_DUTY_AMOUNT IN JAI_CMN_RG_23D_TRXS.duty_amount%TYPE DEFAULT NULL,
104 P_TRANSACTION_ID IN JAI_CMN_RG_23D_TRXS.transaction_source_num%TYPE DEFAULT NULL,
105 P_RECEIPT_DATE IN JAI_CMN_RG_23D_TRXS.receipt_date%TYPE DEFAULT NULL,
106 P_GOODS_ISSUE_ID IN JAI_CMN_RG_23D_TRXS.goods_issue_id%TYPE DEFAULT NULL,
107 P_GOODS_ISSUE_DATE IN JAI_CMN_RG_23D_TRXS.goods_issue_date%TYPE DEFAULT NULL,
108 P_GOODS_ISSUE_QUANTITY IN JAI_CMN_RG_23D_TRXS.goods_issue_quantity%TYPE DEFAULT NULL,
109 P_TRANSACTION_DATE IN JAI_CMN_RG_23D_TRXS.transaction_date%TYPE DEFAULT NULL,
110 P_OPENING_BALANCE_QTY IN JAI_CMN_RG_23D_TRXS.opening_balance_qty%TYPE DEFAULT NULL,
111 P_CLOSING_BALANCE_QTY IN JAI_CMN_RG_23D_TRXS.closing_balance_qty%TYPE DEFAULT NULL,
112 P_BASIC_ED IN JAI_CMN_RG_23D_TRXS.basic_ed%TYPE DEFAULT NULL,
113 P_ADDITIONAL_ED IN JAI_CMN_RG_23D_TRXS.additional_ed%TYPE DEFAULT NULL,
114 P_ADDITIONAL_CVD IN JAI_CMN_RG_23D_TRXS.additional_cvd%TYPE DEFAULT NULL, -- Date 01/11/2006 Bug 5228046 added by SACSETHI
115 P_OTHER_ED IN JAI_CMN_RG_23D_TRXS.other_ed%TYPE DEFAULT NULL,
116 P_CVD IN JAI_CMN_RG_23D_TRXS.cvd%TYPE DEFAULT NULL,
117 P_VENDOR_ID IN JAI_CMN_RG_23D_TRXS.vendor_id%TYPE DEFAULT NULL,
118 P_VENDOR_SITE_ID IN JAI_CMN_RG_23D_TRXS.vendor_site_id%TYPE DEFAULT NULL,
119 P_RECEIPT_NUM IN JAI_CMN_RG_23D_TRXS.receipt_num%TYPE DEFAULT NULL,
120 P_ATTRIBUTE1 IN JAI_CMN_RG_23D_TRXS.attribute1%TYPE DEFAULT NULL,
121 P_ATTRIBUTE2 IN JAI_CMN_RG_23D_TRXS.attribute2%TYPE DEFAULT NULL,
122 P_ATTRIBUTE3 IN JAI_CMN_RG_23D_TRXS.attribute3%TYPE DEFAULT NULL,
123 P_ATTRIBUTE4 IN JAI_CMN_RG_23D_TRXS.attribute4%TYPE DEFAULT NULL,
124 P_ATTRIBUTE5 IN JAI_CMN_RG_23D_TRXS.attribute5%TYPE DEFAULT NULL,
125 P_CONSIGNEE IN JAI_CMN_RG_23D_TRXS.consignee%TYPE DEFAULT NULL,
126 P_MANUFACTURER_NAME IN JAI_CMN_RG_23D_TRXS.manufacturer_name%TYPE DEFAULT NULL,
127 P_MANUFACTURER_ADDRESS IN JAI_CMN_RG_23D_TRXS.manufacturer_address%TYPE DEFAULT NULL,
128 P_MANUFACTURER_RATE_AMT_PER_UN IN JAI_CMN_RG_23D_TRXS.manufacturer_rate_amt_per_unit%TYPE DEFAULT NULL,
129 P_QTY_RECEIVED_FROM_MANUFACTUR IN JAI_CMN_RG_23D_TRXS.qty_received_from_manufacturer%TYPE DEFAULT NULL,
130 P_TOT_AMT_PAID_TO_MANUFACTURER IN JAI_CMN_RG_23D_TRXS.tot_amt_paid_to_manufacturer%TYPE DEFAULT NULL,
131 -- following two parameter added by Vijay Shankar for Bug#3940588 as part of Edu Cess Enhancement
132 P_OTHER_TAX_CREDIT IN JAI_CMN_RG_23D_TRXS.other_tax_credit%TYPE DEFAULT NULL,
133 P_OTHER_TAX_DEBIT IN JAI_CMN_RG_23D_TRXS.other_tax_debit%TYPE DEFAULT NULL,
134 P_SIMULATE_FLAG IN VARCHAR2,
135 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
136 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
137 );
138
139 PROCEDURE update_qty_to_adjust(
140 p_register_id IN NUMBER,
141 p_quantity IN NUMBER,
142 P_SIMULATE_FLAG IN VARCHAR2,
143 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
144 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
145 );
146
147 PROCEDURE update_payment_details(
148 p_register_id IN NUMBER,
149 p_charge_account_id IN NUMBER
150 );
151
152 FUNCTION get_trxn_entry_cnt(
153 p_organization_id IN NUMBER,
154 p_location_id IN NUMBER,
155 p_inventory_item_id IN NUMBER,
156 p_receipt_id IN VARCHAR2,
157 p_transaction_id IN NUMBER
158 ) RETURN NUMBER;
159
160 PROCEDURE get_trxn_type_and_id(
161 p_transaction_type IN OUT NOCOPY VARCHAR2,
162 p_transaction_source IN VARCHAR2,
163 p_transaction_id OUT NOCOPY NUMBER
164 );
165
166 PROCEDURE make_entry
167 (p_org_id IN NUMBER,
168 p_location_id IN NUMBER,
169 p_trans_type IN VARCHAR2,
170 p_item_id IN NUMBER,
171 p_subinv_code IN VARCHAR2,
172 p_pr_uom_code IN VARCHAR2,
173 p_trans_uom_code IN VARCHAR2,
174 p_oth_receipt_id IN NUMBER,
175 p_oth_receipt_date IN DATE,
176 p_oth_receipt_qty IN NUMBER,
177 p_transaction_id IN NUMBER,
178 p_goods_issue_id IN NUMBER,
179 p_goods_issue_date IN DATE,
180 p_goods_issue_qty IN NUMBER,
181 p_trans_date IN DATE,
182 p_creation_date IN DATE,
183 p_created_by IN NUMBER,
184 p_last_update_date IN DATE,
185 p_last_update_login IN NUMBER,
186 p_last_updated_by IN NUMBER);
187
188 PROCEDURE calculate_qty_balances
189 (p_org_id IN NUMBER,
190 p_fin_year IN NUMBER,
191 p_mode VARCHAR2,
192 qty NUMBER,
193 v_opening_Qty IN OUT NOCOPY NUMBER,
194 v_closing_qty IN OUT NOCOPY NUMBER,
195 p_inventory_item_id Number) ;
196
197 PROCEDURE upd_receipt_qty_matched (p_receipt_id in number,
198 p_quantity_applied in number,
199 p_qty_to_adjust Number);
200
201 END jai_cmn_rg_23d_trxs_pkg;
[Home] [Help]