c_invoice_create.sql

Java写的ERP系统

					--	Don't copy zero Product lines - or if already invoiced
					IF (ptr_sl.MovementQty = 0 AND ptr_sl.M_Product_ID IS NOT NULL) THEN
						DBMS_OUTPUT.PUT_LINE('- Skip 0 Qty line -');
						GOTO Next_S_Line;
					END IF;
					--
					AD_Sequence_Next('C_InvoiceLine', o.C_Order_ID, v_NextNo);
					v_LineNo := v_LineNo + 10;
					DBMS_OUTPUT.PUT_LINE('    Line ' || ptr_sl.Line || '  Qty=' || ptr_sl.MovementQty);

					v_ResultStr := 'CreateInvoiceLine from Shipment';
					INSERT INTO C_InvoiceLine
						(C_InvoiceLine_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID,
						Line, Description,
						M_Product_ID, QtyInvoiced, PriceList, PriceActual, 
						PriceLimit, LineNetAmt,
						C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
					VALUES
						(v_NextNo,
						ptr_sl.AD_Client_ID, ptr_sl.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
						p_Invoice_ID, ptr_sl.C_OrderLine_ID, ptr_sl.M_InOutLine_ID,
						v_LineNo, ptr_sl.Description,
						ptr_sl.M_Product_ID, ptr_sl.MovementQty, ptr_sl.PriceList, ptr_sl.PriceActual, 
						ptr_sl.PriceLimit, ptr_sl.MovementQty*ptr_sl.PriceActual,
						ptr_sl.C_Charge_ID, ptr_sl.ChargeAmt, ptr_sl.C_UOM_ID, ptr_sl.C_Tax_ID);
					--
					IF (ptr_sl.M_InOutLine_ID IS NOT NULL) THEN
						UPDATE	M_InOutLine
						  SET	IsInvoiced = 'Y',
								Updated = SysDate
						WHERE	M_InOutLine_ID = ptr_sl.M_InOutLine_ID;
					END IF;
					--
					UPDATE	C_OrderLine
					  SET	QtyInvoiced = QtyInvoiced + ptr_sl.MovementQty,
							DateInvoiced = v_DateInvoiced,
							Updated = SysDate
					WHERE	C_OrderLine_ID = ptr_sl.C_OrderLine_ID;

				<<Next_S_Line>>
					v_ResultStr := 'Fetching_ShipmentLine(*)';
					FETCH Cur_InOutLine INTO ptr_sl;
					EXIT WHEN Cur_InOutLine%NOTFOUND;
				END LOOP;

			--		After (O)rder completely delivered
			ELSIF (o.InvoiceRule = 'O') THEN
				DBMS_OUTPUT.PUT_LINE('- Not implemented -');
				NULL;
			END IF;

			--	Post it
			C_Invoice_Post(NULL, p_Invoice_ID);

		<<Next_Order>>
			v_ResultStr := 'ClosingLine';
			IF (Cur_OrderLine%ISOPEN) THEN
				CLOSE Cur_OrderLine;
			END IF;
			IF (Cur_InOutLine%ISOPEN) THEN
				CLOSE Cur_InOutLine;
			END IF;
		END LOOP;	--	Order Loop
	END IF;		--	p_Selection <> 'Y'


	/**
	 *	Invoice Schedule ======================================================
	 */
	IF (p_PInstance_ID IS NOT NULL) THEN		--	Not when processing a single order
		DECLARE
		--	Invoice Schedule Lines
		CURSOR Cur_InvoiceSchedule (BPartner_ID NUMBER, Org_ID NUMBER) IS
			SELECT	sh.AD_Client_ID, sh.AD_Org_ID, sh.M_InOut_ID, sh.C_DocType_ID, 
				sh.DocumentNo, sh.Description,
				sh.C_BPartner_ID, sh.C_BPartner_Location_ID, sh.C_BPartner_Contact_ID, 
				sh.DateOrdered, sh.C_Order_ID,
				o.SalesRep_ID, 
				NVL(o.C_PaymentTerm_ID, bp.C_PaymentTerm_ID) AS C_PaymentTerm_ID,
				NVL(o.IsDiscountPrinted, bp.IsDiscountPrinted) AS IsDiscountPrinted,
				o.C_Currency_ID,
				NVL(o.PaymentRule, bp.PaymentRule) AS PaymentRule,
				NVL(o.M_PriceList_ID, bp.M_PriceList_ID) AS M_PriceList_ID,
				o.C_Campaign_ID, o.C_Project_ID, o.C_Activity_ID,
				bp.C_InvoiceSchedule_ID, o.InvoiceRule,
				sh.POReference, sh.C_Charge_ID, sh.ChargeAmt,
				--
				sl.AD_Org_ID AS Line_AD_Org_ID, sl.M_InOutLine_ID, sl.Line, 
				sl.Description AS Line_Description,
				sl.C_OrderLine_ID, sl.M_Product_ID, sl.C_UOM_ID, sl.MovementQty,
				NVL(ol.PriceList, 0) AS PriceList, NVL(ol.PriceActual, 0) AS PriceActual, 
				NVL(ol.PriceLimit, 0) AS PriceLimit, 
				ol.C_Tax_ID, --ol.IsTaxIncluded,	--	Could be NULL !!
				ol.C_Charge_ID AS Line_C_Charge_ID, NVL(ol.ChargeAmt, 0) AS Line_ChargeAmt
			FROM	M_InOut sh, M_InOutLine sl, C_BPartner bp, C_InvoiceSchedule si, C_Order o, C_OrderLine ol
			WHERE	sh.M_InOut_ID=sl.M_InOut_ID
			  AND	sh.C_BPartner_ID=bp.C_BPartner_ID
			  AND	bp.C_InvoiceSchedule_ID=si.C_InvoiceSchedule_ID(+)
			  AND	sh.C_Order_ID=o.C_Order_ID
			  AND	sl.C_OrderLine_ID=ol.C_OrderLine_ID
					--	for all BPartners or a specific
			  AND	(BPartner_ID IS NULL OR sh.C_BPartner_ID = BPartner_ID)
					--	for all Organizations or a specific
			  AND	(Org_ID IS NULL OR sh.AD_Org_ID = Org_ID)
					--	completed shipments
			  AND	o.DocStatus IN ('CO', 'CL') AND o.IsSOTrx='Y'
					--	we need to invoice - and not invoiced
			  AND	ol.QtyOrdered <> ol.QtyInvoiced AND sl.IsInvoiced <> 'Y'
					--	Selection
			  AND	((p_Selection = 'Y' AND o.IsSelected='Y')
				OR	(p_Selection <> 'Y' AND
				(
					-- no order or daily
						(si.InvoiceFrequency IS NULL OR si.InvoiceFrequency='D'
						)
					-- weekly invoicing
					OR	(si.InvoiceFrequency='W'
						)
					-- twice monthly invoicing
					OR	(si.InvoiceFrequency='T'
						AND (TRUNC(o.DateOrdered) <= TRUNC(SysDate,'MONTH')+si.InvoiceDayCutoff-1
							AND TRUNC(SysDate) >= TRUNC(o.DateOrdered,'MONTH')+si.InvoiceDay-1)
						OR	(TRUNC(o.DateOrdered) <= TRUNC(SysDate,'MONTH')+si.InvoiceDayCutoff+14
							AND TRUNC(SysDate) >= TRUNC(o.DateOrdered,'MONTH')+si.InvoiceDay+14)
						)
					-- monthly invoicing with shipment on/before cutoff day and on/after invoiceday
					OR	(si.InvoiceFrequency='M' 
						AND TRUNC(o.DateOrdered) <= TRUNC(SysDate,'MONTH')+si.InvoiceDayCutoff-1	-- after cutoff
						AND TRUNC(SysDate) >= TRUNC(o.DateOrdered,'MONTH')+si.InvoiceDay-1		-- after ship day
						)
				)
			  ))
			ORDER BY sh.C_BPartner_ID, sh.M_InOut_ID, sl.Line;
			v_Partner_ID					NUMBER := -1;
			v_InOut_ID						NUMBER := -1;
		BEGIN
			p_Invoice_ID := -1;
			DBMS_OUTPUT.PUT_LINE('  BPartner_ID=' || p_BPartner_ID || ', AD_Org_ID=' || p_AD_Org_ID);
			FOR si IN Cur_InvoiceSchedule (p_BPartner_ID, p_AD_Org_ID) LOOP
				--	Invoice Date from Parameter                  -- or Order
				v_DateInvoiced := NVL(p_DateInvoiced, SysDate);	 -- si.DateOrdered);
				-- 	Summary Invoice only if BP has Schedule setup and selected in invoice
				IF (NOT (si.C_InvoiceSchedule_ID IS NOT NULL AND si.InvoiceRule = 'S')
					AND v_InOut_ID <> si.M_InOut_ID) THEN	--	and new document
					v_Partner_ID := -2;		--	indicate BP change to force new invoice
				END IF;
				--	BPartner changed - New Invoice
				IF (v_Partner_ID <> si.C_BPartner_ID) THEN
					v_Partner_ID := si.C_BPartner_ID;
					--	Post it, if not first time
					IF (p_Invoice_ID <> -1) THEN
						C_Invoice_Post(NULL, p_Invoice_ID);
					END IF;

					--	Get Document Type for Invoice
					v_ResultStr := 'GetDocumentType';
					IF (si.C_Order_ID IS NULL) THEN
						BEGIN
							SELECT	C_DocType_ID, DECODE(IsApproved, 'Y', 'N', 'Y')
							  INTO	v_DocType_ID, v_Approved
							FROM	C_DocType
							WHERE	DocBaseType='ARI' AND ROWNUM=1
							  AND	AD_Client_ID = si.AD_Client_ID
							ORDER BY IsDefault DESC;
							--
							EXCEPTION WHEN	OTHERS THEN
								v_Message := '@NoDefaultInvoice@ ';
								GOTO FINISH_PROCESS;
						END;
					ELSE
						SELECT	od.C_DocTypeInvoice_ID, DECODE(id.IsApproved, 'Y', 'N', 'Y')
						  INTO	v_DocType_ID, v_Approved
						FROM	C_DocType od, C_DocType id, C_Order o
						WHERE	od.C_DocType_ID = o.C_DocType_ID
						  AND	od.C_DocTypeInvoice_ID = id.C_DocType_ID
						  AND	o.C_Order_ID = si.C_Order_ID;
					END IF;

					--	Get other defaults
					AD_Sequence_Next('C_Invoice', si.AD_Client_ID, p_Invoice_ID);
					AD_Sequence_DocType(v_DocType_ID, si.AD_Client_ID, v_DocumentNo);
					--
					DBMS_OUTPUT.PUT_LINE('  SumInvoice_ID=' || p_Invoice_ID || ' DocumentNo=' || v_DocumentNo);
					v_ResultStr := 'InsertSumInvoice ' || p_Invoice_ID;
					INSERT INTO C_Invoice
						(C_Invoice_ID, C_Order_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						IsSOTrx, DocumentNo, DocStatus, DocAction, Processing, Processed,
						C_DocType_ID, C_DocTypeTarget_ID, Description,
						IsApproved, IsTransferred, SalesRep_ID,
						DateInvoiced, DatePrinted, IsPrinted, DateAcct,
						C_PaymentTerm_ID, C_BPartner_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
						POReference, DateOrdered, IsDiscountPrinted,
						C_Currency_ID, PaymentRule, C_Charge_ID, ChargeAmt,
						TotalLines, GrandTotal,
						M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID)
					VALUES
						(p_Invoice_ID, si.C_Order_ID,
						si.AD_Client_ID, si.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
						'Y', v_DocumentNo, 'DR', 'CO', 'N', 'N',
						v_DocType_ID, v_DocType_ID, null,
						v_Approved, 'N', si.SalesRep_ID,
						v_DateInvoiced, NULL, 'N', v_DateInvoiced,	--	DateInvoiced=DateAcct
						si.C_PaymentTerm_ID, si.C_BPartner_ID, si.C_BPartner_Location_ID, si.C_BPartner_Contact_ID,
						si.POReference, si.DateOrdered, si.IsDiscountPrinted,
						si.C_Currency_ID, si.PaymentRule, null, 0,
						0, 0,
						si.M_PriceList_ID, si.C_Campaign_ID, si.C_Project_ID, si.C_Activity_ID);
					--
					IF (p_PInstance_ID IS NOT NULL) THEN
						INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_ID, P_Msg)
						  VALUES (p_PInstance_ID, si.M_InOut_ID, p_Invoice_ID, '@Created@ @Invoice@ ' || v_DocumentNo);
					END IF;
					v_NoRecords := v_NoRecords + 1;
					v_LineNo := 0;
					v_InOut_ID := -1;
				END IF;

			
				--	New Shipment
				IF (si.M_InOut_ID <> v_InOut_ID) THEN
					v_InOut_ID := si.M_InOut_ID;
					--	Get Reference Info
					SELECT	PrintName INTO v_Reference
					FROM	C_DocType dt
					WHERE	C_DocType_ID = si.C_DocType_ID;
					v_Reference := v_Reference || ' ' || si.DocumentNo;		--	Date would come here
					IF (si.POReference IS NOT NULL) THEN
						v_Reference := v_Reference || ' (' || si.POReference || ')';
					END IF;
					v_Reference := v_Reference || ': ' || si.Description;
					--	Reference/Comment Line
					v_ResultStr := 'InsertSumShipmentReference';
					AD_Sequence_Next('C_InvoiceLine', si.C_Order_ID, v_NextNo);
					v_LineNo := v_LineNo + 10;
					INSERT INTO C_InvoiceLine
						(C_InvoiceLine_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID,
						Line, Description,
						M_Product_ID, QtyInvoiced, PriceList, PriceActual, PriceLimit, LineNetAmt,
						C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
					VALUES
						(v_NextNo,
						si.AD_Client_ID, si.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
						p_Invoice_ID, null, null,
						v_LineNo, v_Reference,
						null, 0, 0, 0, 0, 0,
						si.C_Charge_ID, si.ChargeAmt, null, null);
				END IF;


				--	Normal Line
				AD_Sequence_Next('C_InvoiceLine', si.C_Order_ID, v_NextNo);
				v_LineNo := v_LineNo + 10;
				DBMS_OUTPUT.PUT_LINE('    SumLine ' || si.Line || '  Qty=' || si.MovementQty);
				v_ResultStr := 'CreateInvoiceLine_Sum';
				INSERT INTO C_InvoiceLine
					(C_InvoiceLine_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID,
					Line, Description,
					M_Product_ID, QtyInvoiced, PriceList, PriceActual, PriceLimit, LineNetAmt,
					C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
				VALUES
					(v_NextNo,
					si.AD_Client_ID, si.Line_AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
					p_Invoice_ID, si.C_OrderLine_ID, si.M_InOutLine_ID,
					v_LineNo, si.Line_Description,
					si.M_Product_ID, si.MovementQty, si.PriceList, si.PriceActual, si.PriceLimit, si.MovementQty*si.PriceActual,
					si.Line_C_Charge_ID, si.Line_ChargeAmt, si.C_UOM_ID, si.C_Tax_ID);
				--
				UPDATE	M_InOutLine
				  SET	IsInvoiced = 'Y',
						Updated = SysDate
				WHERE	M_InOutLine_ID = si.M_InOutLine_ID;
				--
				IF (si.C_OrderLine_ID IS NOT NULL) THEN
					UPDATE	C_OrderLine
					  SET	QtyInvoiced = QtyInvoiced + si.MovementQty,
							DateInvoiced = v_DateInvoiced,
							Updated = SysDate
					WHERE	C_OrderLine_ID = si.C_OrderLine_ID;
				END IF;
				--
			END LOOP;	--	Invoice Schedule

			--	Post last invoice, if exists
			IF (p_Invoice_ID <> -1) THEN
				C_Invoice_Post(NULL, p_Invoice_ID);
			END IF;

		END;	--	Block
	END IF;	--	PInstance not null


<<FINISH_PROCESS>>
	v_Message := v_Message || '@Created@: ' || v_NoRecords;
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  Update AD_PInstance
		DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
		UPDATE	AD_PInstance
		  SET	Updated = SysDate,
				IsProcessing = 'N',
				Result = 1,					-- success
				ErrorMsg = v_Message
		WHERE	AD_PInstance_ID=p_PInstance_ID;
		COMMIT;
	ELSE
		DBMS_OUTPUT.PUT_LINE('<<C_Invoive_Create finished>> ' || v_Message);
	END IF;
	RETURN;

EXCEPTION
	WHEN  OTHERS THEN
		v_Message := v_ResultStr || ': '  || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_Message);
		ROLLBACK;
		IF (p_PInstance_ID IS NOT NULL) THEN
			UPDATE	AD_PInstance
			  SET	Updated = SysDate,
					IsProcessing = 'N',
					Result = 0,				-- failure
					ErrorMsg = v_Message
			WHERE	AD_PInstance_ID=p_PInstance_ID;
			COMMIT;
		ELSE
			DECLARE
				v_Code	NUMBER			:= SQLCode;
			BEGIN
				INSERT INTO DBA_ErrorLog (DBA_ErrorLog_ID,Created,Code,Msg,Info)
				VALUES (DBA_ErrorLog_Seq.NextVal,SysDate,v_Code,v_ResultStr,'C_Invoice_Create');
			END;
		END IF;
		p_Invoice_ID := 0;				--	Error Indicator
		RETURN;

END C_Invoice_Create;
/