Add Bin Location to Purchase Order

[susanh]

Hi,

I am trying to add the bin location of an item to our Purchase Order form.

Has anyone ever done this? If so, is it an easy process? I am a bit nervous to do this as I don't see the table included in the query for the Purchase Order.

Sue

 

[BigLouie]

You would need to add the IM2 and link it to the PO_20 to get the bin location. HOWEVER since there is already one left outer join (to the terms table) you can either 1) remove the terms table 2) change the terms table link to equal which would require each purchase order to have a term code 3) create a sub-report to get the bin location. My choice would be to change the terms table link to equal almost all purchases orders will have terms and then link the table with a left out join on item and warehouse. Then you can add the bin location.

 

[susanh]

Thanks Louie,

I think it is way beyond my knowledge.

 

[susanh]

Hi Louie,

Well I was hoping this little project would go away, but they asked me again to add it. UGH!

Anyway I was looking again at what you suggested and when I look at the code (Unless I am looking at something wrong), I think the link to the terms table is set to equal.

Here is the From clause in the sql view of the code.

FROM
"PO_20CRWPurchOrderDetail" PO_20CRWPurchOrderDetail,
"PO_25CRWPurchaseOrderHeader" PO_25CRWPurchaseOrderHeader,
"IM2_InventoryItemWhseDetl" IM2_InventoryItemWhseDetl,
"SY0_CompanyParameters" SY0_CompanyParameters,
{ oj "PO1_PurchaseOrderEntryHeader" PO1_PurchaseOrderEntryHeader LEFT OUTER JOIN "APE_TermsCodeMasterfile" APE_TermsCodeMasterfile ON
PO1_PurchaseOrderEntryHeader."TermsCode" = APE_TermsCodeMasterfile."TermsCode"}

Sue

 

[susanh]

Josh Louie,

I just did a sub report on a test company and that seems really simple.

Makes me wonder if I am missing something or did something wrong.

I ran it and the bin location printed out correctly.

 

[BigLouie]

If you would like to make the form to run even faster make these changes.

Change this:

FROM
"PO_20CRWPurchOrderDetail" PO_20CRWPurchOrderDetail,
"PO_25CRWPurchaseOrderHeader" PO_25CRWPurchaseOrderHeader,
"IM2_InventoryItemWhseDetl" IM2_InventoryItemWhseDetl,
"SY0_CompanyParameters" SY0_CompanyParameters,
{ oj "PO1_PurchaseOrderEntryHeader" PO1_PurchaseOrderEntryHeader LEFT OUTER JOIN "APE_TermsCodeMasterfile" APE_TermsCodeMasterfile ON
PO1_PurchaseOrderEntryHeader."TermsCode" = APE_TermsCodeMasterfile."TermsCode"}


To this:

FROM
{ oj "PO1_PurchaseOrderEntryHeader" PO1_PurchaseOrderEntryHeader LEFT OUTER JOIN "APE_TermsCodeMasterfile" APE_TermsCodeMasterfile ON
PO1_PurchaseOrderEntryHeader."TermsCode" = APE_TermsCodeMasterfile."TermsCode"}
"PO_20CRWPurchOrderDetail" PO_20CRWPurchOrderDetail,
"PO_25CRWPurchaseOrderHeader" PO_25CRWPurchaseOrderHeader,
"IM2_InventoryItemWhseDetl" IM2_InventoryItemWhseDetl,
"SY0_CompanyParameters" SY0_CompanyParameters,

 

[susanh]

...ok, I will test it. Then I just need to add the binlocation field in the select portion of the query correct?

Do the sub reports slow the process down? Is that why you recommend the modification of the query?

 

[susanh]

ok...here is the query. Because I just added the bin location in the select clause, and then matched up the item number in the where clause

SELECT
PO_20CRWPurchOrderDetail."PurchaseOrderNumber", PO_20CRWPurchOrderDetail."LineNumber", PO_20CRWPurchOrderDetail."ItemNumber", PO_20CRWPurchOrderDetail."LineType", PO_20CRWPurchOrderDetail."UnitOfMeasure", PO_20CRWPurchOrderDetail."WarehouseCode", PO_20CRWPurchOrderDetail."Description", PO_20CRWPurchOrderDetail."QtyOrdered", PO_20CRWPurchOrderDetail."QtyReceived", PO_20CRWPurchOrderDetail."QtyBckordrd", PO_20CRWPurchOrderDetail."UnitCost", PO_20CRWPurchOrderDetail."OrderExtension", PO_20CRWPurchOrderDetail."SpecialItem", PO_20CRWPurchOrderDetail."SpecialMiscItemDescription", PO_20CRWPurchOrderDetail."SpecialMiscUnitOfMeasure", PO_20CRWPurchOrderDetail."MiscCode", PO_20CRWPurchOrderDetail."MiscChrgDescription", PO_20CRWPurchOrderDetail."DescriptionLine1", PO_20CRWPurchOrderDetail."DescriptionLine2",
PO1_PurchaseOrderEntryHeader."PurchaseOrderDate", PO1_PurchaseOrderEntryHeader."OrderType", PO1_PurchaseOrderEntryHeader."Division", PO1_PurchaseOrderEntryHeader."VendorNumber", PO1_PurchaseOrderEntryHeader."VendorName", PO1_PurchaseOrderEntryHeader."VendorAddress1", PO1_PurchaseOrderEntryHeader."VendorAddress2", PO1_PurchaseOrderEntryHeader."VendorCity", PO1_PurchaseOrderEntryHeader."VendorState", PO1_PurchaseOrderEntryHeader."VendorZipCode", PO1_PurchaseOrderEntryHeader."ShipToName", PO1_PurchaseOrderEntryHeader."ShipToAddress1", PO1_PurchaseOrderEntryHeader."ShipToAddress2", PO1_PurchaseOrderEntryHeader."ShipToCity", PO1_PurchaseOrderEntryHeader."ShipToState", PO1_PurchaseOrderEntryHeader."ShipToZipCode", PO1_PurchaseOrderEntryHeader."OrderStatus", PO1_PurchaseOrderEntryHeader."RequiredExpireDate", PO1_PurchaseOrderEntryHeader."ShipVia", PO1_PurchaseOrderEntryHeader."FOB", PO1_PurchaseOrderEntryHeader."ConfirmTo", PO1_PurchaseOrderEntryHeader."TermsCode", PO1_PurchaseOrderEntryHeader."PrepaidAmount", PO1_PurchaseOrderEntryHeader."TaxableAmount", PO1_PurchaseOrderEntryHeader."NonTaxableAmount", PO1_PurchaseOrderEntryHeader."SalesTaxAmount", PO1_PurchaseOrderEntryHeader."FreightAmount",
PO_25CRWPurchaseOrderHeader."InvalidTaxCalculation",
IM2_InventoryItemWhseDetl."BinLocation",
SY0_CompanyParameters."CompanyName", SY0_CompanyParameters."CompanyAddress1", SY0_CompanyParameters."CompanyAddress2", SY0_CompanyParameters."CompanyAddress3", SY0_CompanyParameters."CompanyAddress4", SY0_CompanyParameters."TelephoneNumber", SY0_CompanyParameters."APRequireDivisions",
APE_TermsCodeMasterfile."Description"
FROM
{ oj "PO1_PurchaseOrderEntryHeader" PO1_PurchaseOrderEntryHeader LEFT OUTER JOIN "APE_TermsCodeMasterfile" APE_TermsCodeMasterfile ON
PO1_PurchaseOrderEntryHeader."TermsCode" = APE_TermsCodeMasterfile."TermsCode"}
"PO_20CRWPurchOrderDetail" PO_20CRWPurchOrderDetail,
"PO_25CRWPurchaseOrderHeader" PO_25CRWPurchaseOrderHeader,
"IM2_InventoryItemWhseDetl" IM2_InventoryItemWhseDetl,
"SY0_CompanyParameters" SY0_CompanyParameters
WHERE
PO_20CRWPurchOrderDetail."PurchaseOrderNumber" = PO1_PurchaseOrderEntryHeader."PurchaseOrderNumber" AND
PO_20CRWPurchOrderDetail."UserFid" = PO_25CRWPurchaseOrderHeader."UserFid" AND
PO_20CRWPurchOrderDetail."PurchaseOrderNumber" = PO_25CRWPurchaseOrderHeader."PurchaseOrderNumber" AND
PO_20CRWPurchOrderDetail."ItemNumber" = IM2_InventoryItemWhseDetl."ItemNumber" AND
PO1_PurchaseOrderEntryHeader."PurchaseOrderNumber" <> SY0_CompanyParameters."CompanyCode"
ORDER BY
PO_20CRWPurchOrderDetail."PurchaseOrderNumber" ASC,
PO_20CRWPurchOrderDetail."LineNumber" ASC